Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sin2win
Starting Member
4 Posts |
Posted - 2010-04-23 : 10:21:53
|
This is driving me crazy... In my database I have names with suffixes, such as (blue H, red H, white P, yellow R...) I need an update query to look at the suffix of the record, keep the name and replace the suffix with something different. So all "XnameX H" records would become "XnameX C" records. Any help would be greatly appreciated!! |
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-04-23 : 10:35:13
|
Can you show us some sample data and specific result?Try this, its maybe correct:UPDATE table_nameSET column_name = LEFT(column_name, LEN(column_name)-1) + SUBSTRING(@variable, LEN(variable)-1, 1) WHERE column_name LIKE '%name%' AND SUBSTRING(column_name, LEN(column_name)-1, 1) = SUBSTRING(@variable, LEN(variable)-1, 1); |
|
|
sin2win
Starting Member
4 Posts |
Posted - 2010-04-23 : 11:46:38
|
Thank you for the quick reply!Sample Data:Red HBlue HWhite PHYellow RHGreen PI need to search through the table for records with the suffix "H" and change all instances' suffix to Q while keeping the original name. I also need to be able to do the same thing for records with suffix "PH" and change all instances' suffix to YQ. I understand what your first posts' code is doing except the @variable/variable part. In the SET statement, the variable would be what I want the suffix changed to, but the @variable/variable confused me in the WHERE clause. |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-04-23 : 12:01:11
|
Does the suffix identify after space character in the name?Also you have a table that stores the new instances? I mean you want multiple update statement or not. |
|
|
sin2win
Starting Member
4 Posts |
Posted - 2010-04-23 : 13:23:04
|
I just need to look in the name column of a table and change the suffix.Example:Red HBlue Hwould become:Red QBlue QI would then change the variables for multiple lettered suffix's.Example:White PHwould become:White YQI need to change the existing record's suffix, not save the results in a different table.Table name = TESTcolorscolumn name = name@variable1 = original suffix "H"@variable2 = desired new suffix "Q"This is my best guess...alter procedure TESTupdateColorSuffix(@variable1 as varchar(5) ,@variable2 as varchar(5) )ASUPDATE TESTcolorsSET name = LEFT(name, LEN(name)-1) + SUBSTRING(@variable2, LEN(name)-1, 1)WHERE name LIKE @variable1 AND SUBSTRING(name, LEN(name)-1, 1) = SUBSTRING(@variable1, LEN(name)-1, 1); |
|
|
sin2win
Starting Member
4 Posts |
Posted - 2010-04-23 : 14:16:45
|
I got it now, your help set me in the right direction. Thank you! |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-04-23 : 15:29:14
|
quote: Originally posted by sin2win I got it now, your help set me in the right direction. Thank you!
That's great! |
|
|
|
|
|
|
|