T-SQL: How to find and replace string patterns

Question

I am not sure whether T-SQL supports regular expressions like other SQL engines. So far I found that I can only use PATINDEX but its matching support seems to be limited.

Here is what I want to for some field in a table using T-SQL:

Find a substring between " IP Address: " and ","
Replace it by " IP Address: ****,"

Here is a more concrete example:

SELECT some_magic("The access is from IP ADDRESS: www.xx.yyy.zz, ...")

I want this to return "The access is from IP ADDRESS: ****, ...".

Is it possible to do this in T-SQL? I am using Azure SQL Database.

As it is it's not clear for me, can you please provide a clear sample data and the desired results? — Ilyes
– Ilyes, Commented Sep 12, 2019 at 20:33
@Sami I tried to add a concise example. Please let me know if it is not clear. — kee
– kee, Commented Sep 12, 2019 at 20:46

Ilyes · Accepted Answer · 2019-09-12 21:14:09Z

2

You can use STUFF() and REPLICATE() functions as

WITH C AS
(
  SELECT Str,
         CHARINDEX(':', Str) +1 S,
         CHARINDEX(',', Str) -1 E
  FROM
  (
    VALUES
    ('The access is from IP ADDRESS: www.xx.yyy.zz, ...')
  ) T(Str)
)
SELECT Str,
       STUFF(Str, S, (E - S)+1, REPLICATE('X', E - S)) Result
       --You could change 'X' with '*' and if you want just 4 stars then get ride of REPLICATE() and use '****'
FROM C;

Demo

edited Sep 12, 2019 at 21:14

answered Sep 12, 2019 at 21:04

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rs. Over a year ago

Good solution, and if he want to only 4 characters, he can just use '***' instead of replicate.

Alan Burstein · Accepted Answer · 2019-09-12 21:25:24Z

@sami beat me to it. Here's what I was working on before he posted:

DECLARE @string VARCHAR(1000) = 'The access is from IP ADDRESS: www.xx.yyy.zz, ...';

SELECT 
  [start] = fnStart.Here,
  [stop]  = fnStop.Here,
  ItemLen = fnStop.Here-fnStart.Here,
  SUBSTRING(@string,fnStart.Here,fnStop.Here-fnStart.Here),
  STUFF(@string,fnStart.Here+1,fnStop.Here-fnStart.Here-1,'****')
FROM        (VALUES('IP ADDRESS: '))                                      AS 
search(string)
CROSS APPLY (VALUES(CHARINDEX(search.string,@string)+LEN(search.string))) AS fnStart(Here)
CROSS APPLY (VALUES(CHARINDEX(',',@string,fnStart.Here+1)))               AS fnStop(Here);

Here's another way using PatternSplitCM. It extracts the IP address, it's length as well as returning the value you are looking for:

DECLARE @string VARCHAR(1000) = 'The access is from IP ADDRESS: www.xx.yyy.zz, ...';

SELECT
  ItemLen   = fnStop.Here-fnStart.Here,
  IPAddress = SUBSTRING(@string,fnStart.Here,fnStop.Here-fnStart.Here),
  NewString = STUFF(@string,fnStart.Here+1,fnStop.Here-fnStart.Here-1,'****')
FROM        (VALUES('IP ADDRESS: '))                                      AS search(string)
CROSS APPLY (VALUES(CHARINDEX(search.string,@string)+LEN(search.string))) AS fnStart(Here)
CROSS APPLY (VALUES(CHARINDEX(',',@string,fnStart.Here+1)))               AS fnStop(Here);

Results:

ItemLen     IPAddress         NewString                                   
----------- ----------------- ---------------------------------------------
14           www.xx.yyy.zz    The access is from IP ADDRESS: ****, ...

Arulmouzhi · Accepted Answer · 2019-09-17 19:56:42Z

Here, the other two methods are:

DECLARE @START_WORD VARCHAR(100)='IP Address:'
DECLARE @END_CHAR CHAR(1)=','

DECLARE @SAMPLE AS TABLE (TEST_DATA VARCHAR(250))

INSERT INTO @SAMPLE (TEST_DATA)
VALUES ('The access is from IP ADDRESS: www.xx.yyy.zz, ...')

SELECT TEST_DATA,
REPLACE(TEST_DATA,
        (
            SUBSTRING((SUBSTRING (TEST_DATA,0,CHARINDEX(@END_CHAR,TEST_DATA))),
            CHARINDEX(@START_WORD,(SUBSTRING (TEST_DATA,0,CHARINDEX(@END_CHAR,TEST_DATA))))+LEN(@START_WORD),
            LEN((SUBSTRING (TEST_DATA,0,CHARINDEX(@END_CHAR,TEST_DATA)))))
        ),
        '****') AS EXPECTED_TEST_DATA_METHOD1,
REPLACE(TEST_DATA,
        (
            SUBSTRING ((RIGHT(TEST_DATA, LEN(TEST_DATA) - PATINDEX('%'+@START_WORD+'%', TEST_DATA)-LEN(@START_WORD) )),
            0,
            CHARINDEX(@END_CHAR,(RIGHT(TEST_DATA, LEN(TEST_DATA) - PATINDEX('%'+@START_WORD+'%', TEST_DATA)-LEN(@START_WORD) ))))
        ),
        '****') AS EXPECTED_TEST_DATA_METHOD2
FROM @SAMPLE

Collectives™ on Stack Overflow

T-SQL: How to find and replace string patterns

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related