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!
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 + ';%';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments