选择“查询”以删除非数字字符

我在具有可变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。确实，所有数据字段的数字前面都有 #。

我尝试了子字符串和 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 服务器上，它可以在 sql Server 2017+ 和 sql azure 上使用(，这是一个实用的解决方案。

首先，它将任何非数字字符替换为 @ 字符。然后，它会删除所有 @ 字符。您可能需要添加您知道 TRANSLATE 调用的第二个参数中可能存在的其他字符。

select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

你可以使用东西和patindex。

stuff(Col, 1, patindex('%[0-9]%', Col)-1, '')

SQL 小提琴

这对我来说很有效：

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

如果数字之间可能有一些字符(例如千位分隔符(，您可以尝试以下操作：

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')

这是一个从字符串中提取所有数字的版本; 即给定I'm 35 years old; I was born in 1982. The average family has 2.4 children.这将返回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(。

为了补充Ken的答案，这处理逗号，空格和括号

--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

使用这个：

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;

结果：

某字符串	(无列名(
sss123ssg99d362sdg	12399362
Hey62Q&*^(N43	6243
零	零

你好
123	123

Create function fn_GetNumbersOnly(@pn varchar(100))
    Returns varchar(max)
    AS
    BEGIN
      Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0
      Select @len = len(@pn)
      while @x <= @len 
      begin
        Select @c = SUBSTRING(@pn,@x,1)
        if ISNUMERIC(@c) = 1 and @c <> '-'
         Select @r = @r + @c
       Select @x = @x +1
      end
    return @r
End

在您的情况下，# 似乎总是在 # 符号之后，因此将 CHARINDEX(( 与 LTRIM(( 和 RTRIM(( 一起使用可能会表现最好。但这里有一个有趣的方法来摆脱任何非数字。它利用计数表和数字表来限制接受哪些字符，然后使用 XML 技术连接回没有非数字字符的单个字符串。这种技术的巧妙之处在于它可以扩展到包含任何允许的字符并删除任何不允许的字符。

DECLARE @ExampleData AS TABLE (Col VARCHAR(100))
INSERT INTO @ExampleData (Col) VALUES ('AB ABCDE # 123'),('ABCDE# 123'),('AB: ABC# 123')
DECLARE @Digits AS TABLE (D CHAR(1))
INSERT INTO @Digits (D) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
;WITH cteTally AS (
SELECT
    I = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
    @Digits d10
    CROSS APPLY @Digits d100
    --add more cross applies to cover longer fields this handles 100
)
SELECT *
FROM
    @ExampleData e
    OUTER APPLY (
    SELECT CleansedPhone = CAST((
    SELECT TOP 100
       SUBSTRING(e.Col,t.I,1)
    FROM
       cteTally t
       INNER JOIN @Digits d
       ON SUBSTRING(e.Col,t.I,1) = d.D
    WHERE
       I <= LEN(e.Col)
    ORDER BY
       t.I
    FOR XML PATH('')) AS VARCHAR(100))) o

在所有这些解决方案中，似乎没有一个去掉"所有"非数字字符。但是克莱门特的方法，如下所述，似乎是最可靠的，并且可以与所有可读的美国键盘字符一起使用......

DECLARE @textval NVARCHAR(300)
SET @textval = '1''2A2B2C2d2e2f2~2!2@2#2$2%2^2&2*2(2)2_2+2`2-2=2{2}2|2[2]22:2"2;2<2>2?2,2.2/2 3'
SELECT REPLACE(TRANSLATE(@textval, ''' abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+`-={}|[]:";<>?,./', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

。产生1222222222222222222222222222222222222223

 Declare @MainTable table(id int identity(1,1),TextField varchar(100))
  INSERT INTO @MainTable (TextField)
 VALUES
 ('6B32E')
 declare @i int=1
  Declare @originalWord varchar(100)=''
  WHile @i<=(Select count(*) from @MainTable)
  BEGIN
  Select @originalWord=TextField from @MainTable where id=@i
 Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0
    Select @len = len(@originalWord)
    declare @pn varchar(100)=@originalWord
    while @x <= @len 
    begin
      Select @c = SUBSTRING(@pn,@x,1)
    if(@c!='')
    BEGIN
            if ISNUMERIC(@c) = 0 and @c <> '-'
    BEGIN
     Select @r = cast(@r as varchar) + cast(replace((SELECT ASCII(@c)-64),'-','') as varchar)
   end
   ELSE
   BEGIN
    Select @r = @r + @c

   END
END

    Select @x = @x +1
    END
    Select @r
  Set @i=@i+1
  END

我为此创建了一个函数

Create FUNCTION RemoveCharacters (@text varchar(30))
RETURNS VARCHAR(30)
AS
BEGIN
declare @index as int 
declare @newtexval as varchar(30)
set @index = (select PATINDEX('%[A-Z.-/?]%', @text))
if (@index =0)
begin 
return @text
end
else
begin 
set @newtexval  = (select STUFF ( @text , @index , 1 , '' ))
return dbo.RemoveCharacters(@newtexval)
end
return 0
END
GO

答案如下：

DECLARE @t TABLE (tVal VARCHAR(100))
INSERT INTO @t VALUES('123')
INSERT INTO @t VALUES('123S')
INSERT INTO @t VALUES('A123,123')
INSERT INTO @t VALUES('a123..A123')

;WITH cte (original, tVal, n)
     AS
     (
         SELECT t.tVal AS original,
                LOWER(t.tVal)  AS tVal,
                65             AS n
         FROM   @t             AS t
         UNION ALL
         SELECT tVal AS original,
                CAST(REPLACE(LOWER(tVal), LOWER(CHAR(n)), '') AS VARCHAR(100)),
                n + 1
         FROM   cte
         WHERE  n <= 90
     )
SELECT t1.tVal  AS OldVal,
       t.tval   AS NewVal
FROM   (
           SELECT original,
                  tVal,
                  ROW_NUMBER() OVER(PARTITION BY tVal + original ORDER BY original) AS Sl
           FROM   cte
           WHERE  PATINDEX('%[a-z]%', tVal) = 0
       ) t
       INNER JOIN @t t1
            ON  t.original = t1.tVal
WHERE  t.sl = 1

您可以创建 SQL CLR 标量函数，以便能够使用正则表达式，如替换模式。

在这里，您可以找到如何创建此类函数的示例。

具有这样的功能将仅通过以下几行解决问题：

SELECT [dbo].[fn_Utils_RegexReplace] ('AB ABCDE # 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('ABCDE# 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('AB: ABC# 123', '[^0-9]', '');

更重要的是，您将能够解决更复杂的问题，因为正则表达式将直接在 T-SQL 语句中带来一个全新的选项世界。

虽然 OP 想要"删除任何不是 0-9 的内容"，但该帖子也被标记为"子字符串"和"patindex"，OP 提到了"语法不正确或其他东西"的担忧。为了解决要求指出"所有数据字段在数字前都有 #"的问题，并提供解决子字符串/patindex 挑战的答案，请考虑以下事项：

/* A sample select */
;WITH SampleValues AS
( SELECT 'AB ABCDE # 123' [Columnofdirtydata]
    UNION ALL SELECT 'AB2: ABC# 123')
SELECT
s.Columnofdirtydata,
f1.pos1,
'['+ f2.substr +']' [InspectOutput]
FROM
SampleValues s
CROSS APPLY (SELECT PATINDEX('%# %',s.Columnofdirtydata) [pos1]) f1
CROSS APPLY (SELECT SUBSTRING(s.Columnofdirtydata, f1.pos1 + LEN('#-'),LEN(s.Columnofdirtydata)) [substr]) f2
/* Using update scenario from OP */
UPDATE t1
SET t1.Columntoupdate = CAST(f2.substr AS INT)
FROM
TableName t1
CROSS APPLY (SELECT PATINDEX('%# %',t1.Columnofdirtydata) [pos1]) f1
CROSS APPLY (SELECT SUBSTRING(t1.Columnofdirtydata, f1.pos1 + LEN('#-'),LEN(t1.Columnofdirtydata)) [substr]) f2

请注意，我对 patindex/子字符串的语法建议是：

请考虑使用 APPLY 作为临时别名一个函数的结果的方法，以便在下一个函数中用作参数。(例如，在 ETL 中(需要在临时表的可更新列中解析出基于参数/位置的子字符串的情况并不少见。如果您需要"调试"并可能修复一些解析逻辑，这种风格会有所帮助。
考虑在子字符串逻辑中使用 LEN('PatternSample'(，以考虑重用此模式或在源数据更改时对其进行调整(而不是"+ 1">
SUBSTRING(( 需要一个长度参数，但它可以大于字符串的长度。因此，如果你在模式之后得到"字符串的其余部分"，你可以只使用"源长度">

声明@STR VARCHAR(400(

DECLARE @specialchars VARCHAR(50( = '%[~，@，#，$，%，&，*，(，(，！^？：]%'

设置@STR = '1， 45 4,3 68.00-'

而 PATINDEX( @specialchars， @STR (> 0

---使用替换功能删除特殊字符

SET @STR = Replace(Replace( @STR， SUBSTRING( @STR， PATINDEX( @specialchars， @STR (， 1

(，''(，''(，'-'，''(，''，'(

选择@STR

SELECT REGEXP_REPLACE( col, '[^[:digit:]]', '' ) AS new_col FROM my_table

相关内容

最新更新

热门标签：