CODEWITHSUNDEEP
t-sqlazure-sql-database
kee
kee

Reputation: 11629

T-SQL: How to find and replace string patterns

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:

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.

Upvotes: 1

Views: 425

Answers (3)

Arulmouzhi
Arulmouzhi

Reputation: 2254

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

Upvotes: 0

Alan Burstein
Alan Burstein

Reputation: 7918

@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: ****, ...

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 2

Related Questions