主页 / coding / 问题 / 79556216
Accepted
SK-USA
Asked: 2025-04-05 04:41:57 +0800 CST

SQL Server RegEx [重复]

  • 772

我需要匹配至少包含 6 位数字的字符串。我遇到了这个问题。

此示例将有助于：

IF OBJECT_iD('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample
CREATE TABLE #Sample(Field VARCHAR(50))
GO

INSERT INTO #Sample (Field) VALUES ('ABC123 ')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC123.')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC1234567&')
INSERT INTO #Sample (Field) VALUES ('ABC12345&')
INSERT INTO #Sample (Field) VALUES ('DataMigrationLog_Bak3')

DECLARE @PAT VARCHAR(500) 

SELECT '%[^a-z0-9 .]%' PAT, * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'

SELECT @PAT = '%[(\d{6})]%'
SELECT @PAT PATVAR, * FROM #Sample WHERE Field LIKE @PAT

SELECT '%[(\d{3})]%' PAT, * FROM #Sample WHERE Field LIKE '%[(\d{3})]%'

当使用这种模式时 -%[(\d{6})]%它给出两行

ABC1234567&
DataMigrationLog_Bak3

我在这里遗漏了什么？

sql-server

1 个回答

  1. Best Answer

    请根据TRANSLATE()功能检查以下解决方案。

    只有 SQL Server 2025 才会开始支持真正的 RegEx。

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (col VARCHAR(100));
INSERT @tbl (col) VALUES
('ABC123 '),
('ABC1'),
('ABC1'),
('ABC123.'),
('ABC123&'),
('ABC123&'),
('ABC1234567&'),
('ABC12345&'),
('DataMigrationLog_Bak3');
-- DDL and sample data population, end

-- SQL Server 2017 onwards
;WITH cte AS 
(
   SELECT col
      , REPLACE(TRANSLATE(col, '0123456789', SPACE(10)),' ','') AS JunkCharacters
   FROM @tbl
)
SELECT col, JunkCharacters
   , REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','') AS CleansedDigits
   , DATALENGTH(REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','')) AS Digit_Counter
FROM cte;

    输出

    科尔 垃圾角色 净化数字 数字计数器
    ABC123 ABC 123 3
    ABC1 ABC 1 1
    ABC1 ABC 1 1
    ABC123。 美国广播公司。 123 3
    ABC123& ABC＆ 123 3
    ABC123& ABC＆ 123 3
    ABC1234567& ABC＆ 1234567 7
    ABC12345& ABC＆ 12345 5
    数据迁移日志_Bak3 数据迁移日志_Bak 3 1
    • 2

相关问题