CODEWITHSUNDEEP
sqlstringsql-server-2008textformat
orBeat
orBeat

Reputation: 125

SQL string remove a substring of unknown length

A very simple problem in SQL that I don't seem to find an easy solution to.

@Input: 'GoodFilter in (2, 3, 100) and BadFilter in (11, 23, 24, 25) and AnotherFilter in (1, 2)'

@DesiredOutput: 'GoodFilter in (2, 3, 100) and AnotherFilter in (1, 2)'

I really would like a simple one line solution to this. Something I tried but with no result:

SELECT REPLACE(@Input, 'BadFilter in (%%) and ', '')

Code example:

DECLARE @Input nvarchar(500) = 'GoodFilter in (2, 3, 100) and BadFilter in (11, 23, 24, 25) and AnotherFilter in (1, 2)'
DECLARE @DesiredOutput nvarchar(500) = 'GoodFilter in (2, 3, 100) and AnotherFilter in (1, 2)'
--do magic to remove BadFilter
--@Input == @DesiredOutput

Upvotes: 0

Views: 612

Answers (2)

orBeat
orBeat

Reputation: 125

With some help from zip i got another idea. I changed the @Input so that the BadFilter always comes last in the @Input. And then the solution looks like:

DECLARE @Input nvarchar(500) = 'GoodFilter in (123,123,123) and AnotherFilter in (1,2,3,4) and BadFilter in (1,2,3)'
SELECT LEFT(@Input, CHARINDEX(' and BadFilter', @Input))

Upvotes: 0

zip
zip

Reputation: 4061

You can use charindex and replace in sql server

select left(@Input, charindex('BadFilter',@Input) - 1) + 
right(@Input, len(@Input) - charindex('AnotherFilter',@Input) + 1)

Upvotes: 1

Related Questions