Solved: SQL Syntax Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Carlos_Mendoza

Thread Starter
Joined
Nov 12, 2011
Messages
124
A few records in a database of mine have become corrupted. I was wondering if someone could write an update query for me (for SQL Server) which would put the records back into the proper formatting. The incorrect formatting is Mendoza ,Carlos. The proper formatting should read Mendoza, Carlos. Is this possible to do through a SQL Update Query? If so can someone give me the syntax that will accomplish this?

Thanks in advance and Merry Christmas!

EDIT ---
I tried this syntax
Code:
update mytable 
set mycolumn = replace(mycolumn, ' ,', ',  '
and that corrected one issue. My next question is, what needs to be added to this statement to drop the comma IF the last text in the field is a comma? That way I only have to run one update statement?
 
Joined
Sep 4, 2003
Messages
4,916
Just add a "WHERE" clause using the RIGHT function. Just replace the comma with an empty string.

NOTE: I have not tested this but in theory it should work so make sure to back up your data or test on a small test subset of data. Also keep in mind that SQL Server does not have a "TRIM" function so this may or may not work depending on whether there is a blank space after the comma. If all else fails you should be able to export the entire table to CSV or other external file format and then make your changes in Excel and then re-import.

update mytable
set mycolumn = replace(mycolumn, ',',' ')
where RIGHT(mycolumn,1) = ','
Rollin
 

Carlos_Mendoza

Thread Starter
Joined
Nov 12, 2011
Messages
124
When I run that syntax it tells me 0 rows affected even tho there are records where the right most character is a ,
I also tried where RIGHT is ', ' to see if possibly the right most character was a space.
 
Joined
Sep 4, 2003
Messages
4,916
If you are going to use the RIGHT function with logic to search for a space you need to search for 2 characters instead of one

where RIGHT(mycolumn,2) = ', '

Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top