sql - sql查询只获取字符串中的数字

假设我有这样的数据:


string 1: 003Preliminary Examination Plan 


string 2: Coordination005 


string 3: Balance1000sheet



我期望的输出是


string 1: 003


string 2: 005


string 3: 1000



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

时间:

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



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



在前面的查询中,我得到了以下结果:

'aaaa1234bbbb3333'>>> > 输出: 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



我没有创建函数的权限,但具有如下文本


["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



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


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



...