TSQL - remove string from column

darkgen · 27 Sep 2019 at 09:50

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.

Code:

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.

chroniclard · 27 Sep 2019 at 10:13

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

chroniclard · 27 Sep 2019 at 10:25

Could string split I guess.
select value from STRING_SPLIT('S-1-5-21-321-999-65123 lisa emma S-1-5-21-321-999-65111', ' ');
gives
1 S-1-5-21-321-999-65123
2 lisa
3 emma
4 S-1-5-21-321-999-65111

Then looping through, discarding what you dont want, putting the rest back together and updating the column.

Dunno if you can do it all in one update statement.

darkgen · 27 Sep 2019 at 10:31

chroniclard said:
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.

chroniclard · 27 Sep 2019 at 11:08

darkgen said:
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.

Code:

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.

Given your data above, which is a little hard to see exactly/a bit flakey is this the expected result?

darkgen · 27 Sep 2019 at 11:43

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

Code:

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

chroniclard · 27 Sep 2019 at 13:31

If I run the below stored procedure it produces this.

Code:

CREATE PROCEDURE [dbo].[FixColumns]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @ColId int, @OriginalCol nvarchar(500), @newcol nvarchar(500);
    DECLARE @ColValue nvarchar(500)
    DECLARE @NewColValue nvarchar(500)
    DECLARE @SearchString nvarchar(10)

DECLARE fix_cursor CURSOR FOR  
SELECT ColId, OriginalCol
FROM testTable
ORDER BY ColId;

 
    SET @SearchString = 'S-1-5-21%';

     OPEN fix_cursor
    FETCH NEXT FROM fix_cursor INTO @ColId, @OriginalCol
 
    IF @@FETCH_STATUS <> 0  
        PRINT '         <<None>>'      
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE col_cursor CURSOR FOR  
        SELECT value
        FROM STRING_SPLIT(@OriginalCol, ' ');
           
                OPEN col_cursor
                    FETCH NEXT FROM col_cursor INTO @ColValue
                    SET @NewColValue = '';
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                    IF @ColValue not like @SearchString AND @ColValue <> ' '
                        BEGIN
                        SET @NewColValue = @NewColValue + ' ' + @ColValue;
                        END
                    FETCH NEXT FROM col_cursor INTO @ColValue
                    END
                        CLOSE col_cursor
                DEALLOCATE col_cursor
                --PRINT '(' + @NewColValue + ')'
                SET @NewColValue = TRIM(@NewColValue);--, 2, LEN(@NewColValue));
                --PRINT '(' + @NewColValue + ')'
                UPDATE testTable SET Newcol = @NewColValue WHERE ColId = @ColId
        FETCH NEXT FROM fix_cursor INTO @ColId, @OriginalCol
        END
 
    CLOSE fix_cursor
    DEALLOCATE fix_cursor

END  

GO

darkgen · 27 Sep 2019 at 13:57

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.

chroniclard · 27 Sep 2019 at 14:02

Trim is just to remove the excess space from the start you can code around it with a RIGHT.

AHarvey · 27 Sep 2019 at 14:06

This should work if all the SIDs are the same length

NAME S-1-5-21-321-222-11111 MY_NAME

SELECT STUFF('NAME S-1-5-21-321-222-11111 MY_NAME', PATINDEX('%S-1-5-21-%', 'NAME S-1-5-21-321-222-11111 MY_NAME') , 23, '');

RETURNS : NAME MY_NAME

So

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

darkgen · 29 Sep 2019 at 09:20

AHarvey said:
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!

morbid42 · 30 Sep 2019 at 08:20

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

darkgen · 30 Sep 2019 at 08:36

morbid42 said:
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 ):

morbid42 · 30 Sep 2019 at 18:15

Is it feasible to use a python script as a middle man, using regex, then import? I know databases dont like imports as one mistake wrecks database (thinking rapid start @ microsoft nav @ sql)

AHarvey · 30 Sep 2019 at 20:36

darkgen said:
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 ):

yes, but run it a couple of times and jobs a goodun.