sql截取字符串




查询仅从字符串中获取数字 (5)

假设我有这样的数据:

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

我期望的输出是

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

我想在sql中实现它。 请帮忙。 提前致谢 :)


在查询中,您可以替换任何字符,它不是数字,没有任何内容:

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

查询:

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

通过以前的查询,我得到了以下结果:

'AAAA1234BBBB3333'>>>>输出:1234

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

以下代码返回所有数字字符:

第一输出:12343333

第二输出: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

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






sql-server