CODEWITHSUNDEEP
sqlsql-server
slashms
slashms

Reputation: 978

Remove a substring in a varchar field from multiple rows of a table

I would like to consult about the best way to remove a certain substring from a varchar field of every row in a table. Let's assume I have a single column table, the column names is "user_list" and it is a varchar field that contain user names seperated by ";". for example:
row1: james;david;moses
row2: mary;moses;terry
row3: ronaldo;messi;zlatan

the lists are not sorted in anyway. I want to crate a SP that gets a username and removes it from every row it appears, for instance if the db is the example above and i got as an input 'moses' I would like it to look like row1: james;david;
row2: mary;terry
row3: ronaldo;messi;zlatan

I want it to be a single update command and not a cursor, and i'm thinking with myself (and now with you) what is the best way to do it.

Thanks!

Upvotes: 0

Views: 104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You have a very poor data structure. SQL has this great structure for storing lists of things. It is called a "table". In particular, you want a junction table instead of storing values as lists.

That said, you cannot always control how data is structured. The following should help:

update table t
    set usernames = replace(replace(';' + usernames + ';', ';' + @UserName + ';', ''), ';;', ';')
    where ';' + usernames + ';' like '%;' + @UserName + ';%';

This will put a semicolon at the beginning and the end of the list. If that is a problem, you can remove them using left() or stuff().

EDIT:

To remove the ; at the beginning, use stuff():

update table t
    set usernames = stuff(replace(replace(';' + usernames + ';', ';' + @UserName + ';', ''), ';;', ';'), 1, 1, '')
    where ';' + usernames + ';' like '%;' + @UserName + ';%';

Upvotes: 2

slashms
slashms

Reputation: 978

Okay so I took what Gordon suggested and to resolve the problem i encountered (can be seen in the comments) I did the following (How didn't I think about it in the first place? :( )

update matan_test 
SET usernames= replace(replace(mail_list,@UserName+';', ''), @UserName, '')
where usernames like '%'+@UserName+'%';

Upvotes: 0

Related Questions