查询只从字符串中获取数字

suppose I have data like this:

假设我有这样的数据:

string 1: 003Preliminary Examination Plan   
string 2: Coordination005  
string 3: Balance1000sheet

The output I expect is

我期望的输出是

string 1: 003
string 2: 005
string 3: 1000

And I want to implement it in sql. Please help. Thanks in advance :)

我想用sql实现它。请帮助。提前谢谢:)

7 个解决方案

#1

First create this UDF

首先创建这个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

Now use the function as

现在使用函数a

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

SQL小提琴

I hope this solved your problem.

我希望这能解决你的问题。

Reference

参考

#2

Try this one -

试试这个,

Query:

查询:

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

Output:

输出:

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

#3

Query:

查询:

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

#4

Please try:

请尝试:

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

#5

With the previous queries I get these results:

通过前面的查询，我得到以下结果:

'AAAA1234BBBB3333' >>>> Output: 1234

“AAAA1234BBBB3333”> > > >输出:1234

'-çã+0!\aº1234' >>>> Output: 0

“ca + 0 !1234º\ ' > > > >输出:0

The code below returns All numeric chars:

下面的代码返回所有的数字字符:

1st output: 12343333

1日输出:12343333

2nd output: 01234

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

#6

declare @puvodni nvarchar(20)
set @puvodni = N'abc1d8e8ttr987avc'

WHILE PATINDEX('%[^0-9]%', @puvodni) > 0 SET @puvodni = REPLACE(@puvodni, SUBSTRING(@puvodni, PATINDEX('%[^0-9]%', @puvodni), 1), '' ) 

SELECT @puvodni

#7

-2

In queries you can for replace any character, that it is not a number, with nothing:

在查询中你可以替换任何字符，它不是一个数字，没有任何东西:

REPLACE( ISNULL( column_name, '' ), '[^0-9]', '' )

#1