Запрос на получение только чисел из строки

Сначала создайте этот UDF

CREATE FUNCTION dbo.udf_GetNumeric
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
  END
  RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Теперь используйте function как

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

Надеюсь, это решило вашу проблему.

Справочник

Попробуй это -

Запрос:

DECLARE @temp TABLE
(
      string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
    ('003Preliminary Examination Plan'),
    ('Coordination005'),
    ('Balance1000sheet')

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(string, pos, LEN(string))
    FROM (
        SELECT string, pos = PATINDEX('%[0-9]%', string)
        FROM @temp
    ) d
) t

Вывод:

----------
003
005
1000

Запрос:

DECLARE @temp TABLE
(
    string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
    ('003Preliminary Examination Plan'),
    ('Coordination005'),
    ('Balance1000sheet')

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                    string) + 1) AS Number
FROM @temp

Пожалуйста, попробуй:

declare @var nvarchar(max)='Balance1000sheet'

SELECT LEFT(Val,PATINDEX('%[^0-9]%', Val+'a')-1) from(
    SELECT SUBSTRING(@var, PATINDEX('%[0-9]%', @var), LEN(@var)) Val
)x

Получение только чисел из строки может быть выполнено в однострочном режиме. Попробуй это :

SUBSTRING('your-string-here', PATINDEX('%[0-9]%', 'your-string-here'), LEN('your-string-here'))

NB: работает только для первого int в строке, например: abc123vfg34 возвращает 123.

С помощью предыдущих запросов я получаю следующие результаты:

'AAAA1234BBBB3333' >>>> Вывод: 1234

'-çã + 0! \ aº1234' >>>> Вывод: 0

Приведенный ниже код возвращает все числовые символы:

1-й вывод: 12343333

2-й выход: 01234

declare @StringAlphaNum varchar(255)
declare @Character varchar
declare @SizeStringAlfaNumerica int
declare @CountCharacter int

set @StringAlphaNum = 'AAAA1234BBBB3333'
set @SizeStringAlfaNumerica = len(@StringAlphaNum)
set @CountCharacter = 1

while isnumeric(@StringAlphaNum) = 0
begin
    while @CountCharacter < @SizeStringAlfaNumerica
        begin
            if substring(@StringAlphaNum,@CountCharacter,1) not like '[0-9]%'
            begin
                set @Character = substring(@StringAlphaNum,@CountCharacter,1)
                set @StringAlphaNum = replace(@StringAlphaNum, @Character, '')
            end
    set @CountCharacter = @CountCharacter + 1
    end
    set @CountCharacter = 0
end
select @StringAlphaNum

У меня не было прав на создание функций, но у меня был текст вроде

["blahblah012345679"]

И нужно было извлечь числа из середины

Обратите внимание, это предполагает, что числа сгруппированы вместе, а не в начале и в конце строки.

select substring(column_name,patindex('%[0-9]%', column_name),patindex('%[0-9][^0-9]%', column_name)-patindex('%[0-9]%', column_name)+1)
from table name

Этот UDF будет работать со всеми типами строк:

CREATE FUNCTION udf_getNumbersFromString (@string varchar(max))
RETURNS varchar(max)
AS
BEGIN
    WHILE  @String like '%[^0-9]%'
    SET    @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')
    RETURN @String
END

Просто небольшая модификация ответа @Epsicron

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                    string) + 1) AS Number
FROM (values ('003Preliminary Examination Plan'),
    ('Coordination005'),
    ('Balance1000sheet')) as a(string)

нет необходимости во временной переменной

Хотя это старая ветка, она первая в поиске Google, я пришел к другому ответу, чем то, что было раньше. Это позволит вам передать ваши критерии того, что должно оставаться в строке, какими бы они ни были. Вы можете поместить его в функцию, которая будет вызывать снова и снова, если хотите.

declare @String VARCHAR(MAX) = '-123.  a    456-78(90)'
declare @MatchExpression VARCHAR(255) = '%[0-9]%'
declare @return varchar(max)

WHILE PatIndex(@MatchExpression, @String) > 0
    begin
    set @return = CONCAT(@return, SUBSTRING(@string,patindex(@matchexpression, @string),1))
    SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
    end
select (@return)

Сначала узнайте начальную длину числа, затем переверните строку, чтобы снова узнать первую позицию (что даст вам конечную позицию числа с конца). Теперь, если вы вычтете 1 из обоих чисел и вычтете его из всей длины строки, вы получите только длину числа. Теперь получите номер с помощью SUBSTRING

declare @fieldName nvarchar(100)='AAAA1221.121BBBB'

declare @lenSt int=(select PATINDEX('%[0-9]%', @fieldName)-1)
declare @lenEnd int=(select PATINDEX('%[0-9]%', REVERSE(@fieldName))-1)

select SUBSTRING(@fieldName, PATINDEX('%[0-9]%', @fieldName), (LEN(@fieldName) - @lenSt -@lenEnd))

Я обнаружил, что этот подход работает примерно в 3 раза быстрее, чем ответ, получивший наибольшее количество голосов. Создайте следующую функцию dbo.GetNumbers:

CREATE FUNCTION dbo.GetNumbers(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN;
    WITH
        Numbers
    AS (
        --Step 1.
        --Get a column of numbers to represent
        --every character position in the @String.
        SELECT 1 AS Number
        UNION ALL
        SELECT Number + 1
        FROM Numbers
        WHERE Number < LEN(@String)
        )
        ,Characters
    AS (
        SELECT Character
        FROM Numbers
        CROSS APPLY (
                --Step 2.
                --Use the column of numbers generated above
                --to tell substring which character to extract.
                SELECT SUBSTRING(@String, Number, 1) AS Character
            ) AS c
        )
    --Step 3.
    --Pattern match to return only numbers from the CTE
    --and use STRING_AGG to rebuild it into a single string.
    SELECT @String = STRING_AGG(Character,'')
    FROM Characters
    WHERE Character LIKE '[0-9]'

    --allows going past the default maximum of 100 loops in the CTE
    OPTION (MAXRECURSION 8000) 

    RETURN @String
END
GO

Тестирование

Тестирование с целью:

SELECT dbo.GetNumbers(InputString) AS Numbers
FROM ( VALUES
         ('003Preliminary Examination Plan') --output: 003
        ,('Coordination005')                 --output: 005
        ,('Balance1000sheet')                --output: 1000
        ,('(111) 222-3333')                  --output: 1112223333
        ,('[email protected]#\-6')           --output: 1380046
    ) testData(InputString)

Тестирование производительности: начните с настройки тестовых данных ...

--Add table to hold test data
CREATE TABLE dbo.NumTest (String VARCHAR(8000)) 

--Make an 8000 character string with mix of numbers and letters
DECLARE @Num VARCHAR(8000) = REPLICATE('12tf56se',800)

--Add this to the test table 500 times
DECLARE @n INT = 0
WHILE @n < 500
BEGIN
    INSERT INTO dbo.NumTest VALUES (@Num)
    SET @n = @n +1
END

Теперь тестируем функцию dbo.GetNumbers:

SELECT dbo.GetNumbers(NumTest.String) AS Numbers
FROM dbo.NumTest -- Time to complete: 1 min 7s

Затем протестируйте UDF из ответа с наибольшим количеством голосов на тех же данных.

SELECT dbo.udf_GetNumeric(NumTest.String)
FROM dbo.NumTest -- Time to complete: 3 mins 12s

Вдохновение для dbo.GetNumbers

Десятичные числа

Если вам нужно, чтобы он обрабатывал десятичные дроби, вы можете использовать любой из следующих подходов, я не обнаружил заметных различий в производительности между ними.

• изменить '[0-9]' на '[0-9.]'
• измените Character LIKE '[0-9]' на ISNUMERIC(Character) = 1 (SQL обрабатывает одно десятичное число как числовое)

Бонус

Вы можете легко адаптировать его к различным требованиям, заменив WHERE Character LIKE '[0-9]' следующими параметрами:

• WHERE Letter LIKE '[a-zA-Z]' --Get only letters
• WHERE Letter LIKE '[0-9a-zA-Z]' --Remove non-alphanumeric
• WHERE Letter LIKE '[^0-9a-zA-Z]' --Get only non-alphanumeric

Функция T-SQL для чтения всех целых чисел из текста и возврата одного по указанному индексу, начиная слева или справа, также используя начальный термин поиска (необязательно):

create or alter function dbo.udf_number_from_text(
    @text nvarchar(max),
    @search_term nvarchar(1000) = N'',
    @number_position tinyint = 1,
    @rtl bit = 0
) returns int
as
    begin
        declare @result int = 0;
        declare @search_term_index int = 0;

        if @text is null or len(@text) = 0 goto exit_label;
        set @text = trim(@text);
        if len(@text) = len(@search_term) goto exit_label;

        if len(@search_term) > 0
            begin
                set @search_term_index = charindex(@search_term, @text);
                if @search_term_index = 0 goto exit_label;
            end;

        if @search_term_index > 0
            if @rtl = 0
                set @text = trim(right(@text, len(@text) - @search_term_index - len(@search_term) + 1));
            else
                set @text = trim(left(@text, @search_term_index - 1));
        if len(@text) = 0 goto exit_label;

        declare @patt_number nvarchar(10) = '%[0-9]%';
        declare @patt_not_number nvarchar(10) = '%[^0-9]%';
        declare @number_start int = 1;
        declare @number_end int;
        declare @found_numbers table (id int identity(1,1), val int);

        while @number_start > 0
        begin
            set @number_start = patindex(@patt_number, @text);
            if @number_start > 0
                begin
                    if @number_start = len(@text)
                        begin
                            insert into @found_numbers(val)
                            select cast(substring(@text, @number_start, 1) as int);

                            break;
                        end;
                    else
                        begin
                            set @text = right(@text, len(@text) - @number_start + 1);
                            set @number_end = patindex(@patt_not_number, @text);

                            if @number_end = 0
                                begin
                                    insert into @found_numbers(val)
                                    select cast(@text as int);

                                    break;
                                end;
                            else
                                begin
                                    insert into @found_numbers(val)
                                    select cast(left(@text, @number_end - 1) as int);

                                    if @number_end = len(@text)
                                        break;
                                    else
                                        begin
                                            set @text = trim(right(@text, len(@text) - @number_end));
                                            if len(@text) = 0 break;
                                        end;
                                end;
                        end;
                end;
        end;

        if @rtl = 0
            select @result = coalesce(a.val, 0)
            from (select row_number() over (order by m.id asc) as c_row, m.val
                    from @found_numbers as m) as a
            where a.c_row = @number_position;
        else
            select @result = coalesce(a.val, 0)
            from (select row_number() over (order by m.id desc) as c_row, m.val
                    from @found_numbers as m) as a
            where a.c_row = @number_position;


        exit_label:
            return @result;
    end;

Пример:

select dbo.udf_number_from text(N'Text text 10 text, 25 term', N'term',2,1);

возвращает 10;

В Oracle

Вы можете получить то, что хотите, используя это:

SUBSTR('ABCD1234EFGH',REGEXP_INSTR ('ABCD1234EFGH', '[[:digit:]]'),REGEXP_COUNT ('ABCD1234EFGH', '[[:digit:]]'))

Образец запроса:

SELECT SUBSTR('003Preliminary Examination Plan  ',REGEXP_INSTR ('003Preliminary Examination Plan  ', '[[:digit:]]'),REGEXP_COUNT ('003Preliminary Examination Plan  ', '[[:digit:]]')) SAMPLE1,
SUBSTR('Coordination005',REGEXP_INSTR ('Coordination005', '[[:digit:]]'),REGEXP_COUNT ('Coordination005', '[[:digit:]]')) SAMPLE2,
SUBSTR('Balance1000sheet',REGEXP_INSTR ('Balance1000sheet', '[[:digit:]]'),REGEXP_COUNT ('Balance1000sheet', '[[:digit:]]')) SAMPLE3 FROM DUAL

Какого черта ...

Это решение отличается от всех предыдущих, а именно:

• Нет необходимости создавать функцию
• Нет необходимости использовать сопоставление с образцом
• Нет необходимости во временной таблице
• В этом решении используется рекурсивное общее табличное выражение (CTE)

Но сначала обратите внимание, что вопрос не указывает, где хранятся такие строки. В моем решении ниже я создаю CTE как быстрый и грязный способ поместить эти строки в какую-то исходную таблицу.

Также обратите внимание: в этом решении используется рекурсивное общее табличное выражение (CTE) - не запутайтесь, если здесь используются два CTE. Первый - просто сделать данные доступными для решения, но для решения этой проблемы требуется только второй CTE. Вы можете адаптировать код, чтобы сделать этот второй запрос CTE вашей существующей таблицей, представлением и т. Д.

И, наконец, мое кодирование является подробным, я пытаюсь использовать имена столбцов и CTE, которые объясняют, что происходит, и вы можете немного упростить это решение. Я добавил несколько псевдотелефонных номеров с некоторым (ожидаемым и нетипичным, в зависимости от обстоятельств) форматированием для удовольствия.

with SOURCE_TABLE as (
    select '003Preliminary Examination Plan' as numberString
    union all select 'Coordination005' as numberString
    union all select 'Balance1000sheet' as numberString
    union all select '1300 456 678' as numberString
    union all select '(012) 995 8322  ' as numberString
    union all select '073263 6122,' as numberString
),
FIRST_CHAR_PROCESSED as (
    select
        len(numberString) as currentStringLength,
        isNull(cast(try_cast(replace(left(numberString, 1),' ','z') as tinyint) as nvarchar),'') as firstCharAsNumeric,
        cast(isNull(cast(try_cast(nullIf(left(numberString, 1),'') as tinyint) as nvarchar),'') as nvarchar(4000)) as newString,
        cast(substring(numberString,2,len(numberString)) as nvarchar) as remainingString
    from SOURCE_TABLE
    union all
    select
        len(remainingString) as currentStringLength,
        cast(try_cast(replace(left(remainingString, 1),' ','z') as tinyint) as nvarchar) as firstCharAsNumeric,
        cast(isNull(newString,'') as nvarchar(3999)) + isNull(cast(try_cast(nullIf(left(remainingString, 1),'') as tinyint) as nvarchar(1)),'') as newString,
        substring(remainingString,2,len(remainingString)) as remainingString
    from FIRST_CHAR_PROCESSED fcp2
    where fcp2.currentStringLength > 1
)
select 
    newString
    ,* -- comment this out when required
from FIRST_CHAR_PROCESSED 
where currentStringLength = 1

Так что здесь происходит?

В основном в нашем CTE мы выбираем первый символ и используем try_cast (см. документацию), чтобы преобразовать его в tinyint (который является достаточно большим типом данных для однозначного числа). Обратите внимание, что правила приведения типов в SQL Server говорят, что пустая строка (или пробел, если на то пошло) будет преобразована в ноль, поэтому nullif добавляется, чтобы заставить пробелы и пустые строки преобразоваться в null (см. обсуждение) (в противном случае наш результат будет включать нулевой символ в любое время пробел встречается в исходных данных).

CTE также возвращает все, что находится после первого символа - и это становится входом для нашего рекурсивного вызова на CTE; другими словами: теперь давайте обработаем следующий символ.

Наконец, поле newString в CTE генерируется (во втором SELECT) посредством конкатенации. С рекурсивными CTE тип данных должен соответствовать между двумя операторами SELECT для любого заданного столбца, включая размер столбца. Поскольку мы знаем, что добавляем (максимум) один символ, мы приводим этот символ к nvarchar (1), а newString (пока что) приводим к nvarchar (3999). В результате получается nvarchar (4000), что соответствует приведению типов, которое мы выполняем в первом SELECT.

Если вы запустите этот запрос и исключите предложение WHERE, вы получите представление о том, что происходит, но строки могут быть в странном порядке. (Вы не обязательно увидите, что все строки, относящиеся к одному входному значению, сгруппированы вместе, но вы все равно сможете следить за ним).

Надеюсь, это интересный вариант, который может помочь некоторым людям, которым требуется решение, основанное исключительно на выражениях.

Если вы используете Postgres и у вас есть такие данные, как «2000 - некоторый образец текста», попробуйте комбинацию подстроки и позиции, иначе, если в вашем сценарии нет разделителя, вам нужно написать регулярное выражение:

SUBSTRING(Column_name from 0 for POSITION('-' in column_name) - 1) as 
number_column_name