从字符串中删除非数字字符

Question

我目前正在进行一个数据转换项目，需要从一个字符串中删除所有字母字符。不幸的是，由于我们不拥有源机器，因此无法创建或使用函数，这使得我在搜索之前发现的方法不可用。

在select语句中做到这一点的最佳方法是什么？速度不是太大的问题，因为这只会运行大约30,000条记录，而且只是一次性语句。

- Michael A

Answer 1

你可以通过一条语句来完成这个操作。你不会真的用200多个REPLACE创建语句吧？！

update tbl
set S = U.clean
from tbl
cross apply
(
    select Substring(tbl.S,v.number,1)
    -- this table will cater for strings up to length 2047
    from master..spt_values v
    where v.type='P' and v.number between 1 and len(tbl.S)
    and Substring(tbl.S,v.number,1) like '[0-9]'
    order by v.number
    for xml path ('')
) U(clean)

使用SQL Fiddle展示此查询及样本数据。

以下复制以备后用：

create table tbl (ID int identity, S varchar(500))
insert tbl select 'asdlfj;390312hr9fasd9uhf012  3or h239ur ' + char(13) + 'asdfasf'
insert tbl select '123'
insert tbl select ''
insert tbl select null
insert tbl select '123 a 124'

结果

ID  S
1   390312990123239
2   123
3   (null)
4   (null)
5   123124

- RichardTheKiwi

---

比我的更聪明，但如果只有需要替换的字母字符，嵌套替换会更容易理解。 :-) - Aaron Bertrand

你说得对。我之前用过这个模式来清理某些字符组（不同的WHERE子句），所以我想重复使用它。 - RichardTheKiwi

你真棒！到目前为止，你的脚本是最好的，我已经测试了20多个脚本 - 其他所有脚本要么不符合SQL 2005标准，要么超级慢。 - Pierre

Answer 2

CTE在这里提供帮助。

;WITH CTE AS 
(
SELECT 

      [ProductNumber] AS OrigProductNumber
      ,CAST([ProductNumber] AS VARCHAR(100)) AS [ProductNumber]           
FROM [AdventureWorks].[Production].[Product]
UNION ALL
SELECT OrigProductNumber
       ,CAST(STUFF([ProductNumber], PATINDEX('%[^0-9]%', [ProductNumber]), 1, '') AS VARCHAR(100) ) AS [ProductNumber]
FROM CTE WHERE PATINDEX('%[^0-9]%', [ProductNumber]) > 0 
)

SELECT * FROM CTE
WHERE PATINDEX('%[^0-9]%', [ProductNumber]) = 0   
OPTION (MAXRECURSION 0)

输出：

OrigProductNumber   ProductNumber
WB-H098                 098
VE-C304-S               304
VE-C304-M               304
VE-C304-L               304
TT-T092                 092

- ClearLogic

Answer 3

RichardTheKiwi的脚本被封装成一个函数，用于在没有交叉应用的情况下进行选择， 同时添加了点号，因为在我的情况下，我在varchar字段中使用它来表示双精度和货币值。

CREATE FUNCTION dbo.ReplaceNonNumericChars (@string VARCHAR(5000))
RETURNS VARCHAR(1000)
AS 
    BEGIN
        SET @string = REPLACE(@string, ',', '.')
        SET @string = (SELECT   SUBSTRING(@string, v.number, 1)
                       FROM     master..spt_values v
                       WHERE    v.type = 'P'
                                AND v.number BETWEEN 1 AND LEN(@string)
                                AND (SUBSTRING(@string, v.number, 1) LIKE '[0-9]'
                                     OR SUBSTRING(@string, v.number, 1) LIKE '[.]')
                       ORDER BY v.number
                      FOR
                       XML PATH('')
                      )
        RETURN @string
    END
GO

感谢RichardTheKiwi的支持 +1

- Pierre

Answer 4

如果你真的不能使用一个函数，我想你可以像这样做：

SELECT REPLACE(REPLACE(REPLACE(LOWER(col),'a',''),'b',''),'c','')
  FROM dbo.table...

显然，这将比我只处理前三个字母更丑陋，但应该能够传达思路。

- Aaron Bertrand

---

是的...最终以这种方式完成了。同意它很丑陋。 - Michael A

一个 T-SQL 函数看起来并不会更漂亮，只是在查询中看起来更好。另一方面，CLR 可以让你使用 RegEx 更整洁地完成这种操作。 - Aaron Bertrand

我完全同意 - 不过这是一个时间敏感的项目 :) - Michael A

@Aaron，@Michael 我认为仍然可能有更简洁的TSQL解决方案...如果字符串中包含奇异字符或不确定，则此答案将需要包括 REPLACE(..,Char(244),'') 等。 - RichardTheKiwi