我在一个变量长度为alpha的列中有脏数据。我只想去除任何不是0-9的内容。
我不想运行函数或过程。我有一个类似的脚本，它只是在文本后面提取数字值，看起来像这样：
Update TableName
set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int)
where Columnofdirtydata like 'Alpha #%'
And ColumntoUpdate is Null
我认为这个方案应该很不错，直到我发现一些数据字段并非像Alpha＃12345789格式那样简单。
需要去除的数据示例：
AB ABCDE # 123
ABCDE# 123
AB: ABC# 123
我只想要那个123。所有数据字段在数字前面都有 # 标记是真的。
我尝试使用 substring 和 PatIndex，但是语法不太正确或者其他原因导致没能成功。请问有什么建议来解决这个问题吗？
请参阅此博客文章，了解如何在SQL Server中从字符串中提取数字。以下是使用您示例中的字符串的示例：
DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'
SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)
如果您的服务器支持TRANSLATE函数（在SQL Server上，它可用于SQL Server 2017+和SQL Azure），这里有一个实用的解决方案。
首先，它将任何非数字字符替换为@字符。 然后，它删除所有@字符。 您可能需要添加其他您知道可能存在于TRANSLATE调用的第二个参数中的字符。
select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')
ABC 123 EFG 456？ - Mikael Eriksson这对我来说效果很好：
CREATE FUNCTION [dbo].[StripNonNumerics]
(
@Temp varchar(255)
)
RETURNS varchar(255)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
然后像这样调用函数，以便在经过过滤的内容旁边看到原始内容：
SELECT Something, dbo.StripNonNumerics(Something) FROM TableA
Set @KeepValues = '%[^0-9].%'，但除此之外整个代码按预期完美运行。 ;) - Annie Lagang如果数字之间可能会有某些字符（例如千位分隔符），您可以尝试以下方法：
declare @table table (DirtyCol varchar(100))
insert into @table values
('AB ABCDE # 123')
,('ABCDE# 123')
,('AB: ABC# 123')
,('AB#')
,('AB # 1 000 000')
,('AB # 1`234`567')
,('AB # (9)(876)(543)')
;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
data as (
select DirtyCol, Col
from @table
cross apply (
select (select C + ''
from (select N, substring(DirtyCol, N, 1) C from tally where N<=datalength(DirtyCol)) [1]
where C between '0' and '9'
order by N
for xml path(''))
) p (Col)
where p.Col is not NULL
)
select DirtyCol, cast(Col as int) IntCol
from data
输出结果为:
DirtyCol IntCol
--------------------- -------
AB ABCDE # 123 123
ABCDE# 123 123
AB: ABC# 123 123
AB # 1 000 000 1000000
AB # 1`234`567 1234567
AB # (9)(876)(543) 9876543
对于更新操作，将ColToUpdate添加到data CTE的选择列表中：
;with num as (...),
data as (
select ColToUpdate, /*DirtyCol, */Col
from ...
)
update data
set ColToUpdate = cast(Col as int)
我来晚了，但我觉得下面这个方法很棒...如果还有人在寻找解决方法的话。
SELECT
(SELECT CAST(CAST((
SELECT SUBSTRING(FieldToStrip, Number, 1)
FROM master..spt_values
WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
AS xml) AS varchar(MAX)))
FROM
SourceTable
CREATE FUNCTION FN_RemoveNonNumeric (@Input NVARCHAR(512))
RETURNS NVARCHAR(512)
AS
BEGIN
DECLARE @Trimmed NVARCHAR(512)
SELECT @Trimmed = @Input
WHILE PATINDEX('%[^0-9]%', @Trimmed) > 0
SELECT @Trimmed = REPLACE(@Trimmed, SUBSTRING(@Trimmed, PATINDEX('%[^0-9]%', @Trimmed), 1), '')
RETURN @Trimmed
END
GO
SELECT dbo.FN_RemoveNonNumeric('ABCDE# 123')
REPLACE(TRANSLATE(SomeString, REPLACE(TRANSLATE(SomeString, '0123456789', '##########'), '#', ''), REPLICATE('#', LEN(REPLACE(TRANSLATE(SomeString, '0123456789', '##########'), '#', '') + 'x') - 1)), '#', '')
演示：
DROP TABLE IF EXISTS #MyTempTable;
CREATE TABLE #MyTempTable (SomeString VARCHAR(255));
INSERT INTO #MyTempTable
VALUES ('ssss123ssg99d362sdg')
, ('hey 62q&*^(n43')
, (NULL)
, ('')
, ('hi')
, ('123');
SELECT SomeString
, REPLACE(TRANSLATE(SomeString, REPLACE(TRANSLATE(SomeString, '0123456789', '##########'), '#', ''), REPLICATE('#', LEN(REPLACE(TRANSLATE(SomeString, '0123456789', '##########'), '#', '') + 'x') - 1)), '#', '')
FROM #MyTempTable;
DROP TABLE IF EXISTS #MyTempTable;
结果：
| SomeString | (无列名) |
|---|---|
| ssss123ssg99d362sdg | 12399362 |
| hey62q&*^(n43 | 6243 |
| NULL | NULL |
| hi | |
| 123 | 123 |
我今年35岁; 我出生于1982年。 平均每个家庭有2.4个孩子。，这将返回35198224。即，它适用于您具有可能已格式化为代码的数字数据（例如#123,456,789 / 123-00005），但如果您希望从文本中提取特定数字（而不是仅仅是数字/数字字符），则不适用。此外，它只处理数字; 因此不会返回负号（-）或句点。）。declare @table table (id bigint not null identity (1,1), data nvarchar(max))
insert @table (data)
values ('hello 123 its 45613 then') --outputs: 12345613
,('1 some other string 98 example 4') --outputs: 1984
,('AB ABCDE # 123') --outputs: 123
,('ABCDE# 123') --outputs: 123
,('AB: ABC# 123') --outputs: 123
; with NonNumerics as (
select id
, data original
--the below line replaces all digits with blanks
, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') nonNumeric
from @table
)
--each iteration of the below CTE removes another non-numeric character from the original string, putting the result into the numerics column
, Numerics as (
select id
, replace(original, substring(nonNumeric,1,1), '') numerics
, replace(nonNumeric, substring(nonNumeric,1,1), '') charsToreplace
, len(replace(nonNumeric, substring(nonNumeric,1,1), '')) charsRemaining
from NonNumerics
union all
select id
, replace(numerics, substring(charsToreplace,1,1), '') numerics
, replace(charsToreplace, substring(charsToreplace,1,1), '') charsToreplace
, len(replace(charsToreplace, substring(charsToreplace,1,1), '')) charsRemaining
from Numerics
where charsRemaining > 0
)
--we select only those strings with `charsRemaining=0`; i.e. the rows for which all non-numeric characters have been removed; there should be 1 row returned for every 1 row in the original data set.
select * from Numerics where charsRemaining = 0
此代码通过将给定字符串中的所有数字（即我们想要的字符）替换为空格来删除它们。然后，它会遍历原始字符串（其中包括数字），并删除剩下的所有字符（即非数字字符），从而仅保留数字。
我们之所以要这样做是因为只有10个数字，而可能的字符数量很大；因此替换该小列表相对较快；然后我们得到了那些实际存在于字符串中的非数字字符列表，所以我们可以替换这个小集合。
该方法利用递归SQL，使用公共表达式（CTE）。
--Handles parentheses, commas, spaces, hyphens..
declare @table table (c varchar(256))
insert into @table
values
('This is a test 111-222-3344'),
('Some Sample Text (111)-222-3344'),
('Hello there 111222 3344 / How are you?'),
('Hello there 111 222 3344 ? How are you?'),
('Hello there 111 222 3344. How are you?')
select
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','')
from @table