如何在sql中删除字段中的特殊字符和前导零
如何在sql中删除字段中的特殊字符和前导零
提问于 2018-07-25 03:55:43
我有一个类似于083_33:152#7 0100的字段，我想一次删除所有特殊字符、空格、前导和尾随零等。我该怎么做呢？输出应该是这样的: 8333152701是我得到的
select * from myTable where REPLACE(LTRIM(REPLACE(part_number, '0', ' ')), ' ', '0') = '8333152701'查询应返回以下内容: 8333152701，谢谢
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-07-26 04:05:28
感谢Alan的细节，它真的很棒。如果任何人需要一个简化的版本，我只是发布这篇文章。
select * from myTable
where replace(ltrim(rtrim(replace(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(isnull(part_number,''),'-',''),'-',''),'*',''),' ',''),'.',''),',',''),'/',''),'\',''),'#',''),':',''),'''',''),'(',''),')',''))), '0', ' '))), ' ', '0') = '8333152701'Stack Overflow用户
发布于 2018-07-25 12:28:06
如果性能很重要，那么剥离非数字字符的最快函数是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.使用并行执行计划运行它
注意，按原样，当字符串为BDA505AD000FAC时，函数返回050。我的理解是您应该获取：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.Stack Overflow用户
发布于 2018-07-25 04:05:48
不是真的像答案(因为嵌套替换)，但这似乎是一个解决方案：
select * from myTable where replace(replace(replace(replace(trim('0 _:#' FROM part_number), '_',''),':',''),'#',''),' ','')='8333152701'Trim只删除开始和结束字符，但您似乎还想删除中间的字符。对于每个字符，我都有一个嵌套的替换。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51506583
复制相关文章
相似问题