sql - 如何从sql中的字段中删除特殊字符和前导零

Question

我有这样的字段 083_33:152#7 0100 我想一次删除所有特殊字符、空格、前导零和尾随零等。我怎样才能做到这一点？输出应该是这样的： 8333152701 这是我所拥有的

select * from myTable where REPLACE(LTRIM(REPLACE(part_number, '0', ' ')), ' ', '0') =  '8333152701'

查询应返回：8333152701 谢谢

标签： sqlsql-servertsql

Answer 1

如果性能很重要，那么去除非数字字符的最快功能是DigitsOnlyEE（您可以通过单击链接获取代码）。修剪前导/尾随 0 和空格的完整解决方案如下所示：

DECLARE @string VARCHAR(100) = '083_33:152#7 0100';

SELECT de.digitsOnly
FROM (VALUES (RTRIM(LTRIM(@string)))) f(s)
CROSS APPLY (VALUES(    -- string, substring start, substring stop, string datalength:
  PATINDEX('%[^0]%',f.s),PATINDEX('%[^0]%',REVERSE(f.s)),LEN(f.s))) f2(ss,sstp,ds) 
CROSS APPLY (VALUES (SUBSTRING(f.s, f2.ss, f2.ds+1-f2.sstp-(f2.ss-1)))) trimmed(string)
CROSS APPLY dbo.digitsOnlyEE(trimmed.string) de;

@shnugo 发布的内容可以快速提供，前提是：1. 将其转换为内联表值函数 2. 使用并行执行计划运行它

请注意，050当字符串按原样返回时，BDA505AD000FAC我的理解是你应该得到：505000但我确信有一个快速解决方法。无论如何，功能：

CREATE FUNCTION dbo.getonlynumbers(@v VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH recCTE AS
(
    SELECT CASE WHEN ASCII(SUBSTRING(@v,1,1)) BETWEEN ASCII(0) AND ASCII(9) THEN SUBSTRING(@v,1,1) ELSE '' END AS Chr
          ,1 AS Pos
    UNION ALL
    SELECT CASE WHEN ASCII(SUBSTRING(@v,r.Pos+1,1)) BETWEEN ASCII(0) AND ASCII(9) THEN SUBSTRING(@v,r.Pos+1,1) ELSE '' END
          ,r.Pos+1
    FROM recCTE r
    WHERE r.Pos<=LEN(@v)
)
,GetOnlyNumbers(CleanedString) AS
(
    SELECT
    (
        SELECT Chr AS [*]
        FROM recCTE
        FOR XML PATH(''),TYPE
    ).value('.','varchar(100)')
)
SELECT REVERSE(B.CleanedFromRear) AS CleanedNumber
FROM GetOnlyNumbers
CROSS APPLY(SELECT SUBSTRING(CleanedString,PATINDEX('%[1-9]%',CleanedString),1000) AS CleanedFromFront) A
CROSS APPLY(SELECT SUBSTRING(REVERSE(CleanedFromFront),PATINDEX('%[1-9]%',REVERSE(CleanedFromFront)),1000) AS CleanedFromRear) B

现在进行性能测试。首先是样本数据：

IF OBJECT_ID('tempdb..#strings') IS NOT NULL DROP TABLE #strings;

DECLARE @default VARCHAR(100) = '083_33:152#7 0100';
SELECT TOP (10000)
  string = 
    ISNULL(CAST(
      REPLICATE('  ', ABS(CHECKSUM(NEWID())%2))+
      REPLICATE('0',  ABS(CHECKSUM(NEWID())%4))+
      REPLACE(REPLACE(LEFT(NEWID(),12),'-','000'),'9', f.rnd)+
      REPLICATE('0',  ABS(CHECKSUM(NEWID())%4)) AS VARCHAR(100)),@default)
INTO #strings 
FROM sys.all_columns, sys.all_columns b
CROSS JOIN
(
  SELECT TOP (ABS(CHECKSUM(NEWID())%5)) f.C+''
  FROM
  (
    SELECT TOP (31) 
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL))^32,
    CHAR((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))^32)
    FROM sys.all_columns) f(N,C)
  ORDER BY NEWID()
  FOR XML PATH('')
) f(rnd);

...接下来进行性能测试。内联表值函数的好处是它们可以使用串行和并行执行计划运行。对于这个测试有一个串行和并行的执行计划。

PRINT 'getonlynumbers - Serial'+CHAR(10)+REPLICATE('-',60)
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(100);

SELECT @x = f.cleanedNumber
FROM #strings s
CROSS APPLY dbo.getonlynumbers(s.string) f
OPTION (MAXDOP 1);

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

PRINT 'getonlynumbers - parallel'+CHAR(10)+REPLICATE('-',60)
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(100);

SELECT @x = f.cleanedNumber
FROM #strings s
CROSS APPLY dbo.getonlynumbers(s.string) f
OPTION (QUERYTRACEON 8649);

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

PRINT 'DigitsOnlyEE - Serial'+CHAR(10)+REPLICATE('-',60)
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(100);

SELECT @x = de.digitsOnly
FROM #strings s
CROSS APPLY (VALUES (RTRIM(LTRIM(s.string)))) f(s)
CROSS APPLY (VALUES(    -- string, substring start, substring stop, string datalength:
  PATINDEX('%[^0]%',f.s),PATINDEX('%[^0]%',REVERSE(f.s)),LEN(f.s))) f2(ss,sstp,ds) 
CROSS APPLY (VALUES (SUBSTRING(f.s, f2.ss, f2.ds+1-f2.sstp-(f2.ss-1)))) trimmed(string)
CROSS APPLY dbo.digitsOnlyEE(trimmed.string) de
OPTION (MAXDOP 1);

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

PRINT 'DigitsOnlyEE - parallel'+CHAR(10)+REPLICATE('-',60)
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(100);

SELECT @x = de.digitsOnly
FROM #strings s
CROSS APPLY (VALUES (RTRIM(LTRIM(s.string)))) f(s)
CROSS APPLY (VALUES(    -- string, substring start, substring stop, string datalength:
  PATINDEX('%[^0]%',f.s),PATINDEX('%[^0]%',REVERSE(f.s)),LEN(f.s))) f2(ss,sstp,ds) 
CROSS APPLY (VALUES (SUBSTRING(f.s, f2.ss, f2.ds+1-f2.sstp-(f2.ss-1)))) trimmed(string)
CROSS APPLY dbo.digitsOnlyEE(trimmed.string) de
OPTION (QUERYTRACEON 8649);

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

结果：

getonlynumbers - Serial
------------------------------------------------------------
Beginning execution loop
2007
2037
2153
Batch execution completed 3 times.

getonlynumbers - parallel
------------------------------------------------------------
Beginning execution loop
513
466
510
Batch execution completed 3 times.

DigitsOnlyEE - Serial
------------------------------------------------------------
Beginning execution loop
250
266
233
Batch execution completed 3 times.

DigitsOnlyEE - parallel
------------------------------------------------------------
Beginning execution loop
63
64
70
Batch execution completed 3 times.