SQL 查询以仅从字符串中获取数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16667251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to get only numbers from a string
提问by Avinash Mehta
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.
我想在 SQL 中实现它。
回答by Luv
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 functionas
现在使用function作为
SELECT dbo.udf_GetNumeric(column_name)
from table_name
I hope this solved your problem.
我希望这解决了你的问题。
回答by Devart
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
回答by Epsicron
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
回答by TechDo
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
回答by Nuno Martins
With the previous queries I get these results:
通过前面的查询，我得到了这些结果：
'AAAA1234BBBB3333' >>>> Output: 1234
'AAAA1234BBBB3333' >>>> 输出：1234
'-??+0!\ao1234' >>>> Output: 0
'-??+0!\ao1234' >>>> 输出：0
The code below returns All numeric chars:
下面的代码返回所有数字字符：
1st output: 12343333
第一个输出：12343333
2nd output: 01234
第二个输出：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
回答by user7844462
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
回答by Kiwi
I did not have rights to create functions but had text like
我没有创建函数的权利，但有类似的文本
["blahblah012345679"]
And needed to extract the numbers out of the middle
并且需要从中间提取数字
Note this assumes the numbers are grouped together and not at the start and end of the string.
请注意，这假定数字组合在一起，而不是在字符串的开头和结尾。
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
回答by IWonderHowLongANameICanTypeInH
Just a little modification to @Epsicron 's answer
只是对@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)
no need for a temporary variable
不需要临时变量
回答by workingmantypething
Although this is an old thread its the first in google search, I came up with a different answer than what came before. This will allow you to pass your criteria for what to keep within a string, whatever that criteria might be. You can put it in a function to call over and over again if you want.
虽然这是一个旧线程，它是谷歌搜索中的第一个线程，但我想出了一个与之前不同的答案。这将允许您传递在字符串中保留内容的标准，无论该标准是什么。如果你愿意，你可以把它放在一个函数中反复调用。
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)
回答by Bartosz X
This UDF will work for all types of strings:
此 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
