•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,673 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,308 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 2617 | Replies: 3 | Solved
 |
•
•
Join Date: Jul 2006
Posts: 17
Reputation: 
Rep Power: 3
Solved Threads: 0
Newbie Poster
need help on replacing a value in a field (MSSQL) 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.
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.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 58
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!
Last edited by hollystyles : Apr 3rd, 2007 at 6:47 am.
•
•
Join Date: Jul 2006
Posts: 17
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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.
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.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- MSSQL Fields Out Of Order (MS SQL)
- Replacing a field separator during a read. (Perl)
- Connecting and retrieving information from an MSSQL Table (VB.NET)
Other Threads in the MS SQL Forum
- Previous Thread: inser into (SQL Server 2000)
- Next Thread: Can i insert a Procedure inside a Trigger????
