Clever Geek Handbook

Request to get only numbers from a string

Suppose I have data like this:

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

Expected Result

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

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

+45


source to share


13 replies


First create this 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 like function 

SELECT dbo.udf_GetNumeric(column_name) 
from table_name


SQL FIDDLE

I hope this solved your problem.

Link

+91


source


Try this option -

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
+34


source


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
+11


source


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
+9


source


With previous queries I get the following results:

'AAAA1234BBBB3333' → → Exit: 1234

'- çã + 0! \ aº1234' → → Exit: 0

The code below returns all numeric characters:

1st exit: 12343333

2nd pin: 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
+3


source 


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
+1


source


I didn't have permission to create functions, but had text like

["blahblah012345679"]

And it was necessary to extract numbers from the middle

Note that the numbers are assumed to be grouped and not at the beginning and end of the line.

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
+1


source


Although this is an old thread, the first on a google search, I found a different answer than the one I had before. This will allow you to pass your criteria to fit within the string, whatever those criteria are. You can put it in a function to be called over and over again if you like.

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


source


In Oracle

You can get what you want using this:

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

Request example:

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
+1


source


SELECT regexp_replace (column-name, '[^ 0-9] *', '', 'g');

0


source


Slight modification to @ Epsicron answer

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

0


source


If you are using Postgres and you have data like "2000 - some sample text" try a combination of substring and position, otherwise, if your script does not have a separator, you need to write a regex:

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


source


In queries, you can replace any character that is not a number and nothing:

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


source






More articles:


All Articles