need help on replacing a value in a field (MSSQL)

[newMeg]

Hi,

I have a table in which there is a field for mobile no. Currently the data in the field has been entered with a dash(-) after the code.

eg-

MobileNo (this is the field name)
-------------------------------------
1. 6016-2880537
2. 6012-3137200

so there is about 400 records in the table now. How possible is it for me to replace the delimeter (-) with '' or to remove the dash from the column mobileno with a single query. I know i can go remove the dash manually in enterprise manager,but it's time consuming.

Pls help..any help is greatly appreciated.

Thanks in advanced.

[hollystyles]

update <tablename> set MobileNo = replace(MobileNo, '-', '')

You don't mention the name of the table so replace <tablename> with the name of your table (minus the chevrons obviously)

I ALWAYS backup my database before these kind of large update operations. In this simple case instead of a full backup you could add a new column to the table first, call it MobileNo_OLD or something, give it the same datatype as MobileNo and then first do:

Update <tablename> set MobileNo_OLD = MobileNo

Now you can always undo your update if you stuff it up, or someone (they always do) changes their mind.

Another tip is to do:

SELECT top 100 replace(MobileNo, '-', '') as [New MobileNo] from <tablename>

To see what the result of your update will look like before you actually commit to an update statement. (I put the 'top 100' in coz I dunno how massive your table might be in number of records, if you have only few hundred you can take that out) Yes you do you said it has 400 numbers! I'm so slow sometimes!

[newMeg]

Hi hollystyles ,

you are always a great help. sorry for the late reply.the query example u said works great and solved my problem.

thanks again and sorry for th elate response.

[hollystyles]

Hey no problem glad I could help.

[Josware]

Hey Hollystyles ¡Thx! You Rocks!