Query to get only numbers from a string

Question

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

User · Answer

With the previous queries I get these results:

'AAAA1234BBBB3333' >>>> Output: 1234

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

The code below returns All numeric chars:

1st output: 12343333

2nd output: 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

User · Answer

Firstly find out the number s starting length then reverse the string to find out the first position again which will give you end position of number from the end   Now if you deduct 1 from both number and deduct it from string whole length you ll get only number length  Now get the number using SUBSTRING declare  fieldName nvarchar 100   AAAA1221 121BBBB   declare  lenSt int  select PATINDEX    0-9      fieldName -1  declare  lenEnd int  select PATINDEX    0-9     REVERSE  fieldName  -1   select SUBSTRING  fieldName  PATINDEX    0-9      fieldName    LEN  fieldName  -  lenSt - lenEnd

User · Answer

Taking other answers into account  i came up with my own  Try this     SUBSTRING  your-string-here   PATINDEX    0-9      your-string-here    LEN  your-string-here      NB  Only works for the first int in the string  ex  abc123vfg34 returns 123

User · Answer

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  gt  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  SELECT dbo udf GetNumeric column name   from table name   SQL FIDDLE  I hope this solved your problem   Reference

User · Answer

This UDF will work for all types of strings   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

User · Answer

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   gt  0     begin     set  return   CONCAT  return  SUBSTRING  string patindex  matchexpression   string  1       SET  String   Stuff  String  PatIndex  MatchExpression   String   1          end select   return

User · Answer

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

User · Answer

For the hell of it    This solution is different to all earlier solutions  viz   There is no need to create a function There is no need to use pattern matching There is no need for a temporary table This solution uses a recursive common table expression  CTE   But first - note the question does not specify where such strings are stored  In my solution below  I create a CTE as a quick and dirty way to put these strings into some kind of  quot source table quot   Note also - this solution uses a recursive common table expression  CTE  - so don t get confused by the usage of two CTEs here  The first is simply to make the data avaliable to the solution - but it is only the second CTE that is required in order to solve this problem  You can adapt the code to make this second CTE query your existing table  view  etc  Lastly - my coding is verbose  trying to use column and CTE names that explain what is going on and you might be able to simplify this solution a little  I ve added in a few pseudo phone numbers with some  expected and atypical  as the case may be  formatting for the fun of it  with SOURCE TABLE as       select  003Preliminary Examination Plan  as numberString     union all select  Coordination005  as numberString     union all select  Balance1000sheet  as numberString     union all select  1300 456 678  as numberString     union all select   012  995 8322    as numberString     union all select  073263 6122   as numberString    FIRST CHAR PROCESSED as       select         len numberString  as currentStringLength          isNull cast try cast replace left numberString  1       z   as tinyint  as nvarchar      as firstCharAsNumeric          cast isNull cast try cast nullIf left numberString  1      as tinyint  as nvarchar      as nvarchar 4000   as newString          cast substring numberString 2 len numberString   as nvarchar  as remainingString     from SOURCE TABLE     union all     select         len remainingString  as currentStringLength          cast try cast replace left remainingString  1       z   as tinyint  as nvarchar  as firstCharAsNumeric          cast isNull newString     as nvarchar 3999     isNull cast try cast nullIf left remainingString  1      as tinyint  as nvarchar 1       as newString          substring remainingString 2 len remainingString   as remainingString     from FIRST CHAR PROCESSED fcp2     where fcp2 currentStringLength  gt  1   select      newString        -- comment this out when required from FIRST CHAR PROCESSED  where currentStringLength   1  So what s going on here  Basically in our CTE we are selecting the first character and using try cast  see docs  to cast it to a tinyint  which is a large enough data type for a single-digit numeral   Note that the type-casting rules in SQL Server say that an empty string  or a space  for that matter  will resolve to zero  so the nullif is added to force spaces and empty strings to resolve to null  see discussion   otherwise our result would include a zero character any time a space is encountered in the source data   The CTE also returns everything after the first character - and that becomes the input to our recursive call on the CTE  in other words  now let s process the next character  Lastly  the field newString in the CTE is generated  in the second SELECT  via concatenation  With recursive CTEs the data type must match between the two SELECT statements for any given column - including the column size  Because we know we are adding  at most  a single character  we are casting that character to nvarchar 1  and we are casting the newString  so far  as nvarchar 3999   Concatenated  the result will be nvarchar 4000  - which matches the type casting we carry out in the first SELECT  If you run this query and exclude the WHERE clause  you ll get a sense of what s going on - but the rows may be in a strange order   You won t necessarily see all rows relating to a single input value grouped together - but you should still be able to follow   Hope it s an interesting option that may help a few people wanting a strictly expression-based solution

User · Answer

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

User · Answer

In Oracle  You can get what you want using this   SUBSTR  ABCD1234EFGH  REGEXP INSTR   ABCD1234EFGH       digit      REGEXP COUNT   ABCD1234EFGH       digit         Sample Query    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

User · Answer

T-SQL function to read all the integers from text and return the one at the indicated index  starting from left or right  also using a starting search term  optional    create or alter function dbo udf number from text       text nvarchar max        search term nvarchar 1000    N         number position tinyint   1       rtl bit   0   returns int as     begin         declare  result int   0          declare  search term index int   0           if  text is null or len  text    0 goto exit label          set  text   trim  text           if len  text    len  search term  goto exit label           if len  search term   gt  0             begin                 set  search term index   charindex  search term   text                   if  search term index   0 goto exit label              end           if  search term index  gt  0             if  rtl   0                 set  text   trim right  text  len  text  -  search term index - len  search term    1                else                 set  text   trim left  text   search term index - 1            if len  text    0 goto exit label           declare  patt number nvarchar 10       0-9             declare  patt not number nvarchar 10        0-9             declare  number start int   1          declare  number end int          declare  found numbers table  id int identity 1 1   val int            while  number start  gt  0         begin             set  number start   patindex  patt number   text               if  number start  gt  0                 begin                     if  number start   len  text                          begin                             insert into  found numbers val                              select cast substring  text   number start  1  as int                                break                          end                      else                         begin                             set  text   right  text  len  text  -  number start   1                               set  number end   patindex  patt not number   text                                if  number end   0                                 begin                                     insert into  found numbers val                                      select cast  text as int                                        break                                  end                              else                                 begin                                     insert into  found numbers val                                      select cast left  text   number end - 1  as int                                        if  number end   len  text                                          break                                      else                                         begin                                             set  text   trim right  text  len  text  -  number end                                                if len  text    0 break                                          end                                  end                          end                  end          end           if  rtl   0             select  result   coalesce a val  0              from  select row number   over  order by m id asc  as c row  m val                     from  found numbers as m  as a             where a c row    number position          else             select  result   coalesce a val  0              from  select row number   over  order by m id desc  as c row  m val                     from  found numbers as m  as a             where a c row    number position            exit label              return  result      end    Example   select dbo udf number from text N Text text 10 text  25 term   N term  2 1     returns 10

User · Answer

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

User · Answer

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

User · Answer

If you are using Postgres and you have data like  2000 - some sample text  then try substring and position combination  otherwise if in your scenario there is no delimiter  you need to write regex    SUBSTRING Column name from 0 for POSITION  -  in column name  - 1  as  number column name

User · Answer

Just a little modification to  Epsicron  s 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

User · Answer

declare  puvodni nvarchar 20  set  puvodni   N abc1d8e8ttr987avc   WHILE PATINDEX     0-9      puvodni   gt  0 SET  puvodni   REPLACE  puvodni  SUBSTRING  puvodni  PATINDEX     0-9      puvodni   1          SELECT  puvodni

[sql] Query to get only numbers from a string

Examples related to sql

Examples related to sql-server