TSQL - remove string from column

[darkgen]

I'm trying to find the easiest way to strip out strings which match S-1-5-21-* (stale user SID) from a space deliminated column where there could be multiple matches.

Orange    paul john david
Apple    Paul S-1-5-21-100-123-43211 john
Banana    S-1-5-21-321-999-65123 lisa emma S-1-5-21-321-999-65111
Carrot    S-1-5-21-321-222-11111
>
Orange    paul john david
Apple    Paul john
Banana    lisa emma 
Carrot

Looking at PATINDEX,SUBSTRING,REPLACE or some elements of REGEX perhaps.

TIA, Paul.

 

[darkgen]

so you want to replace all of the underlined bits? S-1-5-21-321-999-65123 lisa emma S-1-5-21-321-999-65111

Remove the entire string which matches S-1-5-21* from the column so that only resolvable user accounts remain.

I suspect a sub-query is needed; I'm just useless at trying fathom that with the match.

 

[darkgen]

Apologies; should have made it clearer (you cannot tabulate in the embedded code tags):

Col1      Col2
Orange    paul john david
Apple     Paul S-1-5-21-100-123-43211 john
Banana    S-1-5-21-321-999-65123 lisa emma S-1-5-21-321-999-65111
Carrot    S-1-5-21-321-222-11111
>
Col1      Col2
Orange    paul john david
Apple     Paul john
Banana    lisa emma
Carrot

 

[darkgen]

Yikes OK thanks.

Eh, look like SQL server 2012 doesn't recognise TRIM as an in-built function ): I do have access to 2017; let me have a thunk.

 

[darkgen]

SELECT STUFF(COLUMN_NAME, PATINDEX('%S-1-5-21-%', COLUMN_NAME), 23, '')

Insert each row with a SID to a temp table, loop through updating and copy back

So I cheated with

CASE WHEN UserNames LIKE '%S-1-5-21-%' THEN STUFF(UserNames, PATINDEX('%S-1-5-21-%', UserNames), 48, '') ELSE UserNames END AS CleanedUsers

No doubt an inefficient and clumsy way to do this but I'm only processing less than 5000 records.

Thanks, Paul!

 

[darkgen]

Your life is being made difficult by the root cause of "not great" data

I do not disagree. In this instance it's associated user logon data from Citrix. I've asked the vendor numerous times but apparently there isn't an easy way to expunge entries where a domain user has been deleted.

For this exercise I'm just trying to clean up the reporting without the need to touch the Controllers. However, it would make sense to write a powershell script to strip stale entries from associated users.

Edit:
AND

CASE WHEN UserNames LIKE '%S-1-5-21-%' THEN STUFF(UserNames, PATINDEX('%S-1-5-21-%', UserNames), 48, '') ELSE UserNames END AS CleanedUsers

Only strips the first instance of the pattern ):