January 18, 2009 at 2:25 pm
Hello,
I need to update the values in a field to make them consistent.
Current Values
ColumnName
ABC5X7
DEF_5X7
GHI5X7
JKL_5X7
...
Desired Values
ColumnName
ABC_5X7
DEF_5X7
GHI_5X7
JKL_5X7
...
I need to insert the underscore before the 5X7 but not affect those values that currently have the underscore.
Thanks very much.
January 18, 2009 at 3:31 pm
This is completly untested, make sure you try that on a backup copy of the data.
Also your question seems incomplete so there might be so caveat to this solution.
TEST.
SELECT ColumnName, LEFT(ColumnName, LEN(ColumnName - 3)) + '-' + RIGHT(ColumnName, 3) AS CorrectedColumnName FROM dbo.BaseTable WHERE ColumnName LIKE '%5X7' AND ColumnName NOT LIKE '%_5X7'
Once you got the first part working and showing you only the rows you need to update and shows you the right data, then turn the statement into an update like so :
-- UPDATE dbo.baseTable SET ColumnName = LEFT(ColumnName, LEN(ColumnName - 3)) + '-' + RIGHT(ColumnName, 3) WHERE ColumnName LIKE '%5X7' AND ColumnName NOT LIKE '%_5X7'
--Please note that this will not work if you have some trailing spaces, like of a CHAR or NCHAR column. That's because the len statement will ignore those characters... hence returning the wrong number of characters which will in turn truncate your data.
January 18, 2009 at 3:45 pm
May I suggest that you look up the SUBSTRING function in Books On Line and use that. It is simple enough and I believe that you can compose the necessary T-SQL statement with that function. Give it a try and if you are still not able to do what you want say so and someone will assist you further.
Ninja's_RGR'_us
Sorry - you type a lot faster than I do .... my apologies
January 18, 2009 at 5:15 pm
Here's another way to go:
--Test:
Select
Replace( Replace(ColumnName, '_5X7', '5X7'), '5X7', '_5X7')
From YourTable
Where ColumnName like '5X7'
--change:
Update YourTable
Set ColumnName
= Replace( Replace(ColumnName, '_5X7', '5X7'), '5X7', '_5X7')
Where ColumnName like '5X7'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 18, 2009 at 9:04 pm
Simple solution. Thanks Barry.
January 18, 2009 at 9:32 pm
RBarryYoung-
It worked perfectly after changing the WHERE to LIKE '%5X7'
Thank you.
January 18, 2009 at 10:40 pm
Thanks for the feed back.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 19, 2009 at 5:31 am
RBarryYoung (1/18/2009)
Here's another way to go:
--Test:
Select
Replace( Replace(ColumnName, '_5X7', '5X7'), '5X7', '_5X7')
From YourTable
Where ColumnName like '5X7'
--change:
Update YourTable
Set ColumnName
= Replace( Replace(ColumnName, '_5X7', '5X7'), '5X7', '_5X7')
Where ColumnName like '5X7'
Thanks Barry, I had forgotten about that one.
Now that I think about it, I remember that a replace replaced too much data so I decided to forget about that solution (had to go to backups to recover). Of course, if I had tested more with the select first, then done the update in transaction, I wouldn't have had that problem 5 years ago ;).
January 21, 2009 at 4:53 am
Have a look at the function STUFF. Will help you most in thiis case 😉
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply