问题:提取任意字符串中第一次出现的数字组合。
USE [AdventureWorks2012];
GO
DECLARE @t_TB TABLE
(
ColVal VARCHAR(20)
)
;
INSERT INTO @t_TB VALUES
('ABC123')
, ('ENA329')
, ('EEE22W33')
, ('F1W2C3E8')
, ('DDDDDDD')
;
SELECT
ColVal
, LEFT(SubStringTemp, PATINDEX('%[^0-9]%', SubStringTemp + ' ') - 1) as new_num
FROM (
SELECT ColVal,SubStringTemp = SUBSTRING(ColVal, FirstNumberPosition, LEN(ColVal))
FROM (
SELECT ColVal, PATINDEX('%[0-9]%', ColVal) AS FirstNumberPosition
FROM @t_TB
) AS RemoveBeginPart
) AS RemoveEndPart
/*
col new_num
-------------------- --------------------
ABC123 123
ENA329 329
EEE22W33 22
F1W2C3E8 1
DDDDDDD
*/