Remove char from string w/different criteria

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

Remove char from string w/different criteria

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 11:35:48

I have a string (8,2,9) or (2,8,9) or (9,2,8). I need to remove the 8 plus either the comma in front (if the last character) or the 8 plus following comma.

My queries to find these are below:
1.SELECT sec_role FROM SECURITY where sec_role like '%,8,%'
2.SELECT security_role FROM SECURITY where left(sec_role, 2) = '8,'

I tried to use in a update/replace function, but cannot get the right syntax. Is there an easier whay to do this?

UPDATE SECURITY
SET SEC_ROLE = REPLACE (CASE
WHEN (SELECT sec_role FROM SECURITY where sec_role like '%,8,%') then ''
WHEN (SELECT sec_role FROM SECURITY where left(sec_role, 2) = '8,') then''
END))
FROM SECURITY

Thanks,
dz

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-23 : 11:45:37

[code]UPDATE SECURITY
SET SEC_ROLE = REPLACE(REPLACE(SEC_ROLE, ',8', ''), '8,', '')[/code]