代码之家 › 专栏 › 技术社区 › kee

T-SQL:如何查找和替换字符串模式

azure-sql-database tsql

1

kee · 技术社区 · 6 年前

我不确定T-SQL是否像其他SQL引擎一样支持正则表达式。到目前为止,我发现我只能使用PATINDEX,但它的匹配支持似乎是有限的。

下面是我希望使用T-SQL为表中的某个字段设置的内容:

在“IP地址:”和“,”之间查找子字符串
替换为“IP地址:***,”

下面是一个更具体的例子:

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

我希望它返回“访问来自IP地址:***,…”。

可以在T-SQL中执行此操作吗?我正在使用Azure SQL数据库。

0 回复 | 直到 6 年前

1

2

Ilyes 6 年前

你可以用 STUFF() 和 REPLICATE() 职能

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

2

1

Alan Burstein 6 年前

@萨米赢了我。以下是我在他发布之前所做的工作:

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);

这里有另一种使用 PatternSplitCM . 它提取IP地址及其长度,并返回您要查找的值:

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);

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

3

0

Arulmouzhi 6 年前

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