Replace function - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replace function

Author

Topic

dz60
Starting Member

8 Posts

Posted - 2010-10-08 : 17:47:41

I am trying to remove certain numbers from assigned roles.

Roles Before Script: 2,5,8,15,25,28,35,38

Expected Roles After Script: 2,5,15,25,28,35,38

Actual Roles After Script: 2,5,15,25,235,38

My script is taking the 28,35 and making it 235.

UPDATE Role_table
SET ROLE = REPLACE(REPLACE(ROLE, ',8', ''), '8,','')
FROM Role_table
where ROLE like '%,8%'
or ROLE like '8,%'
or ROLE = '8'

How can I get the replace to only update wthe examples in teh Like statement?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 17:51:01

Is normalizing your schema a possibility? Having data like this is not a good design. These values should be in their own rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dz60
Starting Member

8 Posts

Posted - 2010-10-11 : 08:57:13

No, unfortunately this has been inheranted and there are about 12 apps runnign against 32 databases using this format. This is the first tiem we are running into values with double digets that need to eb updated.

dz

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 09:12:58

declare @test varchar(255)
set @test = '8,2,5,8,15,25,28,35,38,8'
select
@test as BeforeRepl,
replace(replace(',,'+@test+',,',',8,',','),',,','') as AfterRepl

No, you're never too old to Yak'n'Roll if you're too young to die.

dz60
Starting Member

8 Posts

Posted - 2010-10-11 : 09:23:14

The '8,2,5,8,15,25,28,35,38,8' string is only one example. Many other variations are possible. i am not sure how to set this up to work with all known variations.

Can you tell me moe about what you suggested or point me to a place I can research and understand better?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 09:28:53

Test this to remove the 8 from your Role column:

update Role_Table
set Role = replace(replace(',,'+Role+',,',',8,',','),',,','')
where ','+Role+',' like '%,8,%'

No, you're never too old to Yak'n'Roll if you're too young to die.

dz60
Starting Member

8 Posts

Posted - 2010-10-11 : 10:08:08

Webfred,

update Role_Table
set Role = replace(replace(',,'+Role+',,',',8,',','),',,','')
where ','+Role+',' like '%,8,%' It works, bce I am trying to pick it apaert and figure out what you did.

REPLACE (string_expression,string_pattern,string_replacement)
replace(',,'+Role+',,',',8,',',')

There are two commas + role + two more commas. What is that for?

Thanks
dz

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 10:12:44

I want each number in the string to be ,<number>, so it doesn't matter if the number that we want to remove is at the bginning, in the middle or at the end of the string.
So I decided to temp. add them in front and after the string.
But to replace them back to <nothing> I decided to use double comma so the outer replace will not remove any needed commas.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 11:15:12

You just and only have to change 8 by 6.
Also this is possible so you can set @relValue to the number that has to be removed:

declare @replValue varchar(10)
set @replValue = '6'
update Role_Table
set Role = replace(replace(',,'+Role+',,',','+@replValue+',',','),',,','')
where ','+Role+',' like '%,'+@replValue+',%'

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 11:15:52

Oh! You have removed your last post?

No, you're never too old to Yak'n'Roll if you're too young to die.

dz60
Starting Member

8 Posts

Posted - 2010-10-11 : 11:17:26

If I wanted to replace the number 8 with a number 6, what would that look like. I think an actual number visual will help me better understand the formula.

the string looks like this:
'6,2,5,15,25,6,26,35,38,6'

I want it to look like this:
8,2,5,15,25,8,26,35,38,8'

Thanks in advance for your patience.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 11:19:36

Ah! Now you want to replace instead of remove!
Wait...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 11:27:14

Try this:
declare @oldValue varchar(10)
declare @newValue varchar(10)
set @oldValue = '6'
set @newValue = '8'

update Role_Table
set Role = replace(replace(',,'+Role+',,',','+@oldValue+',',','+@newValue+','),',,','')
where ','+Role+',' like '%,'+@oldValue+',%'

No, you're never too old to Yak'n'Roll if you're too young to die.

dz60
Starting Member

8 Posts

Posted - 2010-10-11 : 11:41:18

That was a great example! It is one i understand. I tested both with different variations of the string and they worked!

Thanks loads!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 13:06:56

welcome