How to delete values of a string between a char and expression - SQL Server

Question

I would like to delete all values in a column 'F250N' of type string between a known char ']' and an expression '[G56]' in Table 'T250'

This would also need to cover cases where a preceding char of ] cannot be found eg. at the start of the string.

Eg:

 SYSADMIN[G63]Z-GHQ[G62]Z-WE[G56]Z-MEX[G56]Z-NAZ[G56]Z-LAS[G56]Z-LAN[G56]Z-CEE[G56]Z-APAC[G56]Z-CAD[G56]

Would become:

 SYSADMIN[G63]Z-GHQ[G62]

and:

LVN-CHNG[G62]LVN-READ[G56]LVN-FULL[G63]

Would become:

LVN-CHNG[G62]LVN-FULL[G63]

So far I have tried something like:

update T250 
set F250N = replace(F250N, '[G56]', '') 
from T250

however this does not take into account the portion of the string preceding [G56] to the last ]

when you state, "a known char ']'", what about the border case when a token terminated with [G56] would be the first? — Cee McSharpface, Feb 09 '17 at 17:19
Your desired output is much more involved than your question. Your second example isn't removing characters after the `]` character but instead after the entire `[G62]LVN-` portion. Please be more specific on the rules here. — iamdave, Feb 09 '17 at 17:20
in the longer example, all remaining "tokens" end with a [G56], so we need to cut out all occurrences, not just the first. — Cee McSharpface, Feb 09 '17 at 17:22
Yes it would need to take the border case into account. Have updated the question, haven't posted in a while. — Adam, Feb 09 '17 at 17:24
i do not think it could be done by simple `replace` query, i am trying to do it — LONG, Feb 09 '17 at 17:26

Cee McSharpface · Answer 1 · 2017-02-09T18:12:14.570

Could not think of a way to solve this inline, in an UPDATE clause. So there would be this procedural approach:

set nocount on

declare
  @T250 table(pk int primary key, F250N nvarchar(max))
declare
  @s nvarchar(max),
  @pk int,
  @i int,
  @left nvarchar(max),
  @right nvarchar(max),
  @lenbefore int

insert @T250 values
  (1, N'SYSADMIN[G63]Z-GHQ[G62]Z-WE[G56]Z-MEX[G56]Z-NAZ[G56]Z-LAS[G56]Z-LAN[G56]Z-CEE[G56]Z-APAC[G56]Z-CAD[G56]'),
  (2,N'LVN-CHNG[G62]LVN-READ[G56]LVN-FULL[G63]')

--before
select F250N from @T250

declare c cursor for select pk, F250N from @T250
where F250N like N'%[[]G56]%' --skip rows that do not even contain the marker
open c
fetch next from c into @pk,@s
while @@FETCH_STATUS=0 begin
  --handle single value
  set @lenbefore=len(@s)
  while @s like N'%[[]G56]%' begin
    set @i=CHARINDEX(N'[G56]', @s)
    if @i>0 begin
      set @right=SUBSTRING(@s, @i+len(N'[G56]'),len(@s))
      set @left = REVERSE(substring(@s, 1, @i - 1))
      if @i>0 begin
        set @left=reverse(SUBSTRING(@left,@i,len(@left)))
        set @s=@left+@right
      end else begin
        set @s=@right
      end
    end else begin
      break
    end
    --fuse
    if len(@s)=@lenbefore break
  end
  --persist
  update t set t.F250N=@s from @T250 t where t.pk=@pk
  --advance
  fetch next from c into @pk, @s
end
close c
deallocate c

--after
select F250N from @T250

I did not test for the border cases, but at least it will not go into an endless loop.

score 0 · Answer 2 · edited May 23 '17 at 12:02

I still can't decide if I love or hate T-SQL for not supporting regex without all the tom-foolery like Oracle does...

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. - Programming Aphorism

Honestly though, this looks like a regular expression problem (database design questions aside). While T-SQL is regex averse, it can be supported via CLR.

Read more here. Godspeed!

dfundako · Answer 3 · 2017-02-09T18:14:29.653

Here is my crack at it. First, go find a split function. Here is the one I used: http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

This works for both of your samples. Provide more and we will see.

IF OBJECT_ID('tempdb..#holdingtable') IS NOT NULL
    DROP TABLE #holdingtable

--DECLARE @ VARCHAR(1000) = REPLACE('SYSADMIN[G63]Z-GHQ[G62]Z-WE[G56]Z-MEX[G56]Z-NAZ[G56]Z-LAS[G56]Z-LAN[G56]Z-CEE[G56]Z-APAC[G56]Z-CAD[G56]', '[G56]', ',')

declare @ varchar(1000) = REPLACE('LVN-READ[G56]LVN-CHNG[G62]LVN-READ[G56]LVN-FULL[G63]LVN-READ[G56]', '[G56]', ',')
SELECT *
INTO #holdingtable
FROM [fnSplitString](@, ',')

IF (
        SELECT COUNT(*)
        FROM #holdingtable
        WHERE splitdata LIKE '%[[]%'
        ) > 1
BEGIN
        ;

    WITH cte
    AS (
        SELECT LEFT(splitdata, LEN(splitdata) - CHARINDEX(']', REVERSE(splitdata)) + 1)  AS col_
        FROM #holdingtable
        WHERE LEFT(splitdata, LEN(splitdata) - CHARINDEX(']', REVERSE(splitdata)) + 1) LIKE '%[[]%'
        )
    SELECT col_ AS [text()]
    FROM cte
    ORDER BY col_
    FOR XML PATH('')
END
ELSE IF (
        SELECT COUNT(*)
        FROM #holdingtable
        WHERE splitdata LIKE '%[[]%'
        ) = 1
BEGIN
    SELECT LEFT(splitdata, LEN(splitdata) - CHARINDEX(']', REVERSE(splitdata)) + 1)
    FROM #holdingtable
    WHERE LEFT(splitdata, LEN(splitdata) - CHARINDEX(']', REVERSE(splitdata)) + 1) LIKE '%[[]%'
END

How to delete values of a string between a char and expression - SQL Server

3 Answers3