做到这一点的一种方法是
CREATE FUNCTION dbo.udf_GetCleanedAlphaNumeric (@alphanumeric VARCHAR(100))
RETURNS TABLE
AS
RETURN
(SELECT SUBSTRING(cleaned, PATINDEX('%[^0]%', cleaned + 'x'), 8000) AS Final
FROM (VALUES (SUBSTRING(@alphanumeric, 1, PATINDEX('%[,;\/]%', @alphanumeric + ';') - 1))) ca1(prefix)
CROSS APPLY (VALUES ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(prefix, ' ', ''), 'a', '')
, 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', '')
, 'g', ''), 'h', ''), 'i', ''), 'j', ''), 'k', '')
, 'l', ''), 'm', ''), 'n', ''), 'o', ''), 'p', '')
, 'q', ''), 'r', ''), 's', ''), 't', ''), 'u', '')
, 'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', '') )) ca2(cleaned))
然后
SELECT alphanumeric,
Final
FROM temp
CROSS APPLY dbo.udf_GetCleanedAlphaNumeric(alphanumeric)
- 首先提取任意
,;\/
之前的子字符串(如果存在)
- 然后go 掉所有字母或空格(在以后的版本中可以更简洁地使用
TRANSLATE
功能,就像在编辑历史记录中一样)
- 然后删除前导零
(DB Fiddle)