append to existing data

[babeo]

Hello,

Could someone show me a quick way to append data to the existing data please.

I have a character field (mon1) in the database length of 4 characters, but currently it only has values of 3 characters such as '101' or '122', '284' etc.
I want to add 1 more character '1' in front of each piece of data in this field (so it will become '1101', '1122', '1284', etc.) and there are about 500 records need to be updated. Notes that there are no pattern of this field, I means the number is not an increased constant number but just any number.

thanks

 

[jhonty]

Hi,

Its character field so no need to worry just do

update <tab_name>
set <field> = '1'+<field name>
where <condition_if_required>

all the best.

 

[babeo]

Hi jhonty

I forgot to mention that there are other rows (about 300 rows) with the right values of 4 characters for this field, eg '2020', '1099', etc

So I am thinking about how to recognize the rows with only 3 characters of this field then update, otherwise don't.

Could I use like this?

update <tab_name>
set <field> = '1'+<field name>
where <field> like &quot;???&quot;

Thanks

 

[dickiebird]

......where char_length(mon1) = 3

;-) Dickie Bird
Honi soit qui mal y pense

 

[babeo]

Thanks Dickie Bird

 

[JohnWoo]

Hi..
This is useful, but can you tell me what the <field name> stand for in your code

update <tab_name>
set <field> = '1'+<field name>
where char_length(mon1) = 3

 

[babeo]

This is what we mean:

update <tab_name>
set mon1 = '1'+ mon1
where char_length(mon1) = 3

 

[JohnWoo]

How do you add brackets to existing phone numbers?

For example 222 222-xxxx.

I want to change it to +(222) 222-xxxx. I want the first 3 numbers in +().

 

[dickiebird]

update tablename
set telno = '+(' + substr(telno,1,3) + ')' + substr(telno,4) Dickie Bird
Honi soit qui mal y pense

 

[JohnWoo]

Thanks!!!

I've another question. How do you remove the dash in a phone number?

For example +(222) 222-xxxx
I want to change it to +(222) 222 xxxx

 

[qyllr]

hello,

try using the substring function...

222 222-xxxx

select &quot;+(&quot;+substring(telno,1,3)+&quot&quot;+&quot; &quot;+substring(telno,5,3)+&quot; &quot;+substring(telno,9,4)
from tableName
go

hth,
q.