Updateing fields

[Nozy]

Greetings

Newbee to SQL and database admin.

My problem is that I have a table with a field with numerical data. I need to keep this data intact but what need to do is put in two digits extra.

example

CLI - 01204 491219
Updated - 33 1204 491219

How can I do this with a script.

Cheers
Nozy

 

[MaffewW]

Nozy, you say you want to keep your data intact but then your example data replaces the leadng 0 with nothing, I guess its to add an international dialing code so this example should do the trick

update yourtable set CLI = '33 ' + substring(CLI,2,13)

if yopu want to leave the leading '0' in just

update yourtable set CLI = '33 ' + CLI

HTH

Matt

Brighton, UK

 

[SQLBill]

Before you run an UPDATE, check to see what your column length is set to allow.

-SQLBill

 

[tlbroadbent]

Matt has provided a good solution. I add this solution only to demonstrate the STUFF function. The STUFF function can be very useful when inserting strings between characters in a column or when replacing characters (by position) in a column.

--Insert at the beginning of a column
Update YourTable
Set CLI = Stuff(1, 0, CLI, '33 ')
Where <some criterion>
And CLI Not Like '33 %'

--Insert at the beginning of a column and replace the leading character
Update YourTable
Set CLI = Stuff(1, 1, CLI, '33 ')
Where <some criterion>
And CLI Not Like '33 %'

--Insert between characters 3 and 4
Update YourTable
Set CLI = Stuff(4, 0, CLI, '33')
Where <some criterion>

--Replace characters 3 and 4
Update YourTable
Set CLI = Stuff(3, 2, CLI, '33')
Where <some criterion>


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm