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

Ответ 1

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

Ответ 2

Вы можете использовать материал и patindex.

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

SQL Fiddle

Ответ 3

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

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)

Ответ 4

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

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

Ответ 5

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

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

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

Ответ 6

Чтобы добавить к ответ 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

Ответ 7

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

Ответ 8

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

Ответ 9

 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 [email protected]

 Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0

    Select @len = len(@originalWord)
    declare @pn varchar(100)[email protected]
    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 @[email protected]+1
  END

Ответ 10

Здесь версия, которая вытягивает все цифры из строки; т.е. с учетом 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).

Ответ 11

Я создал функцию для этого

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

Ответ 12

Вот ответ:

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

Ответ 13

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

1. Я удалил одинарные кавычки.

2. Затем я использовал заменить "," на "." ,

Наверняка это кому-нибудь поможет

" & txtFinalscore.Text.Replace(",", ".") & "

Ответ 14

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

Ответ 15

Вы можете создать скалярную функцию 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.