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.
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:
SELECT top 100REPLACE(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!
Last edited by hollystyles; Apr 3rd, 2007 at 7:47 am.
Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.
This thread is more than three months old
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Marked 
