CODEWITHSUNDEEP
sqlsql-server
gaffcz
gaffcz

Reputation: 3479

REPLACE/REMOVE similar substrings from string

How to remove all the \trxxx substrings from following test string?

blabla\tr568\tr1136\tr1704\tr2272-\tr2840\tr3408\tr3976\tr4544\tr5112\tr5680blabla

To get blabla-blabla?

I've tried regex, but I failed:

select REPLACE('blabla\tr568\tr1136\tr1704\tr2272-\tr2840\tr3408\tr3976\tr4544\tr5112\tr5680blabla', '\tr[0-9]+', '')

Upvotes: 0

Views: 85

Answers (4)

Terrypin
Terrypin

Reputation: 11

In my text editor I used the following Regex expression Replace \tx[0-9]+ With nothing.

EDIT: Hmm, there seems a bug in this composition window. The expression above has two backslashes at the start as you see

in this screenshot

But only one appears in the post.

-- Terry, East Grinstead, UK

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

SQL Server lacks Regex. You can use CLR integration (preferred if you have millions of records) or use recursive query. Example of recursive query with some test cases below:

WITH TestCases AS
(
    SELECT * FROM (VALUES
    ('blabla\tr568\tr1136\tr1704\tr2272-\tr2840\tr3408\tr3976\tr4544\tr5112\tr5680blabla'),
    ('\tr23SomeText\tr1'),
    ('bla99bla\tr568\tr1136\tr1704\tr2272-\tr2840\tr3408\tr3976\tr4544\tr5112\tr5680rock'),
    (''),
    (NULL)) T(Expr)
), Cte AS
(
    SELECT 1 R, ROW_NUMBER() OVER (ORDER BY Expr) Rec, CONVERT(varchar(MAX), Expr) Expr
    FROM TestCases
    UNION ALL
    SELECT R+1 R, Rec, CASE
        WHEN PATINDEX('%\tr[0-9][0-9][0-9][0-9]%', Expr)>0 THEN STUFF(Expr, PATINDEX('%\tr[0-9][0-9][0-9][0-9]%', Expr), 7, '')
        WHEN PATINDEX('%\tr[0-9][0-9][0-9]%', Expr)>0 THEN STUFF(Expr, PATINDEX('%\tr[0-9][0-9][0-9]%', Expr), 6, '')
        WHEN PATINDEX('%\tr[0-9][0-9]%', Expr)>0 THEN STUFF(Expr, PATINDEX('%\tr[0-9][0-9]%', Expr), 5, '')
        ELSE STUFF(Expr, PATINDEX('%\tr[0-9]%', Expr), 4, '') END
    FROM Cte
    WHERE PATINDEX('%\tr[0-9]%', Expr)>0
)
SELECT TOP 1 WITH TIES Expr FROM Cte
ORDER BY (ROW_NUMBER() OVER (PARTITION BY Rec ORDER BY R DESC))

It yields:

bla99bla-rock
blabla-blabla
NULL

SomeText

Upvotes: 3

nazark
nazark

Reputation: 1240

try

declare @s varchar(Max)='blabla\tr568\tr1136\tr1704\tr2272-\tr2840\tr3408\tr3976\tr4544\tr5112\tr5680blabla'

While PatIndex('%\tr[0-9]%', @s) > 0
        Set @s = REPLACE(@s,SUBSTRING(@s,PATINDEX('%[0-9]%',@s),1),'')

set @s= REPLACE(@s,'\tr','')
select @s

Upvotes: 1

Dheeraj Kumar
Dheeraj Kumar

Reputation: 4175

Pseudo Code:

  1. get CHARINDEX(1st String) starting from 0
  2. get CHARINDEX(1st String) starting from last
  3. STUFF(yourString, result of step1+1, result of step2, '')

Upvotes: 0

Related Questions