Выберите запрос для удаления нечисловых символов

См. этот сообщение в блоге об извлечении чисел. из строк в 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)

Вы можете использовать вещи и патиндекс.

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

скрипт SQL

В случае, если между цифрами возможны некоторые символы (например, разделители тысяч), вы можете попробовать следующее:

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)

Это хорошо работает для меня:

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

Вот элегантное решение, если ваш сервер поддерживает функцию TRANSLATE (на сервере sql она доступна на сервере sql 2017+, а также в sql azure).

Во-первых, он заменяет любые нечисловые символы символом @. Затем он удаляет все символы @. Возможно, вам потребуется добавить дополнительные символы, которые, как вы знаете, могут присутствовать во втором параметре вызова TRANSLATE.

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

Вот версия, которая извлекает все цифры из строки; то есть, учитывая 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

Этот код работает, удаляя все цифры (то есть символы, которые мы хотим) из заданных строк, заменяя их пробелами. Затем он проходит через исходную строку (которая включает в себя цифры), удаляя все оставшиеся символы (т. е. нечисловые символы), оставляя, таким образом, только цифры.

Причина, по которой мы делаем это в 2 шага, а не просто удаляем все нечисловые символы, заключается в том, что цифр всего 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

В вашем случае кажется, что # всегда будет после символа #, поэтому использование 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

Довольно поздно на вечеринку, я нашел следующее, с которым я работал блестяще ... если кто-то все еще ищет

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

ОБЪЯВИТЬ @STR VARCHAR(400)

DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),!^?:]%'

НАБОР @STR = '1, 45 4,3 68.00-'

ПОКА PATINDEX( @specialchars, @STR ) > 0

--- Удалить специальные символы с помощью функции замены

SET @STR = Заменить(Заменить(ЗАМЕНИТЬ( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',''), ' ','')

ВЫБЕРИТЕ @STR