How to get the first numbers in a SQL string

Question

I need to get the first number(s) in a string:

• 1-2-3 --> 1
• 45-6 --> 45
• 777 8 --> 777
• 9ab10 --> 9

How can I do that with SQL?

Answer 1

You can use function PATINDEX to search for the first non-numeric character

SELECT LEFT(s,ISNULL(NULLIF(PATINDEX('%[^0-9]%',s),0)-1,LEN(s)))
FROM
  (
    VALUES
      ('1-2-3'),
      ('45-6'),
      ('777 8'),
      ('9ab10'),
      ('123'),
      ('abc')
  ) v(s)

If string starts with non-numeric prefix then we can delete this prefix first of all

SELECT
  LEFT(s,ISNULL(NULLIF(PATINDEX('%[^0-9]%',s),0)-1,LEN(s)))
FROM
  (
    SELECT
      -- delete non-numberic prefix
      IIF(s NOT LIKE '[0-9]%',STUFF(s,1,PATINDEX('%[0-9]%',s)-1,''),s) s
    FROM
      (
        VALUES
          ('1-2-3'),
          ('45-6'),
          ('777 8'),
          ('9ab10'),
          ('123'),
          ('abc'),
          ('#12test'),
          ('No999-888')
      ) v(s)
  ) q

Answer 2

see Turning a Comma Separated string into individual rows it can help you to separate the string' and then check by IsNumber

Answer 3

You could also use of substring() with patindex() function in order to get first numeric values

SELECT SUBSTRING(DATA, 1, PATINDEX('%[^0-9]%', DATA)-1) FROM <table_name>

Result :

1
45
777
9

Answer 4

This can be achieved with a bit of string splitting and window functions, in a completely set based manner that utilises no User Defined Functions:

-- Test Data
declare @v table(v nvarchar(10));
insert into @v values('1-2-3'),('45-6'),('777 8'),('a9b10'),('abc9b10'),('123'),('abc');

            -- Create table with 10 rows in it
with t(t) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)
            -- Self join this table 6 times to cover very large string lengths (10*10*10*10*10*10 = 1,000,000 rows) and row number to get incremental list
    ,n(n) as (select top (select max(len(v)) from @v) row_number() over (order by (select null)) from t t1,t t2,t t3,t t4,t t5,t t6)
            -- Split the strings using this list of incremental numbers and use window functions to determine:
    ,v    as (select v.v
                    ,n.n
                        -- The actual character at this position
                    ,substring(v.v,n.n,1) as l
                        -- Is it numeric?
                    ,case when substring(v.v,n.n,1) like '[0-9]' then 1 else 2 end as i
                        -- Order all the numeric values
                    ,rank() over (partition by v.v order by case when substring(v.v,n.n,1) like '[0-9]' then 1 else 2 end, n.n) as c
                        -- Group the numeric rows together by summing up the numeric checks
                    ,sum(case when substring(v.v,n.n,1) like '[0-9]' then 0 else 1 end) over (partition by v.v order by n.n) as g
              from n
                  join @v as v
                      on n.n <= len(v.v)
             )
select v.v
                -- Use a FOR XML statement to concatenate the additional values together
      ,v.l + isnull((select '' + v2.l
                    from v as v2
                    where v.v = v2.v
                        and v.n < v2.n
                        and (v2.c = 1
                            or v.g = v2.g
                            )
                    for xml path('')
                    ),'') as n
from v
where c = 1     -- Only select the first grouping of numbers
    and i = 1
order by v.v;

Output:

+---------+-----+
|    v    |  n  |
+---------+-----+
| 123     | 123 |
| 1-2-3   |   1 |
| 45-6    |  45 |
| 777 8   | 777 |
| a9b10   |   9 |
| abc9b10 |   9 |
+---------+-----+