In SQL Server can you find the first number in a string?

Question

I have a varchar(50) sql server column with data like this:

RawData
----------------------------
Washington 40 New Orleans 32
Detroit 27 St. Louis 23

I'm trying to parse out the data so I have something like this:

WinningTeam     WinningTeamScore      LosingTeam    LosingTeamScore
-----------     ----------------      ----------    ---------------
Washington      40                    New Orleans   32
Detroit         27                    St. Louis     23

I'm stuck. I was going to use charindex to find the first space, however some city names (St. Louis, New York, etc) have spaces in the names.

Is there a way to identify the position of the first number in a string?

Thanks

use PATINDEX instead of CHARINDEX => http://msdn.microsoft.com/library/ms188395.aspx — fnurglewitz, Nov 14 '12 at 16:15
Thanks tr3. Why don't you post an answer so I can give you an upvode. — codingguy3000, Nov 14 '12 at 16:21

score 44 · Answer 1 · answered Nov 14 '12 at 16:14

44

Is there a way to identify the position of the first number in a string?

Yes

SELECT PATINDEX('%[0-9]%','Washington 40 New Orleans 32')

PATINDEX returns 0 if the pattern can't be found or the 1 based index of the beginning of the match otherwise.

answered Nov 14 '12 at 16:14

Martin Smith

438,706
87
741
845

1

i thought of regular expressions - but that may be Oracle only.. not sure. – Randy Nov 14 '12 at 16:15
1

@Randy - They can be used in SQL Server but need you to install a CLR function first. No out of the box support, – Martin Smith Nov 14 '12 at 16:18

score 16 · Accepted Answer · answered Nov 14 '12 at 16:25

16

You can use the PATINDEX function instead of CHARINDEX, here lies the documentation ;)

answered Nov 14 '12 at 16:25

fnurglewitz

2,097
14
21

score 5 · Answer 3 · answered Nov 14 '12 at 16:34

Here is a very ugly implementation of PATINDEX() which returns the data in the multiple columns:

SELECT rtrim(substring(RawData, 1, PATINDEX('%[0-9]%', RawData) -1)) WinningTeam,
    ltrim(rtrim(substring(RawData, PATINDEX('%[0-9]%', RawData), 2))) WinningTeamScore,
    reverse(ltrim(rtrim(substring(reverse(SUBSTRING(RawData, 1, LEN(rawdata) - (PATINDEX('%[aA-zZ]%', REVERSE(rawData)) - PATINDEX('%[0-9]%', reverse(RawData))))), 1, PATINDEX('%[0-9]%', SUBSTRING(RawData, 1, LEN(rawdata) - (PATINDEX('%[aA-zZ]%', REVERSE(rawData)) - PATINDEX('%[0-9]%', reverse(RawData))))))))) LosingTeam,
    substring(reverse(RawData), PATINDEX('%[0-9]%', reverse(RawData)), (PATINDEX('%[aA-zZ]%', REVERSE(rawData)) - PATINDEX('%[0-9]%', reverse(RawData)))) LosingTeamScore
from yourtable

See SQL Fiddle with Demo

score 4 · Answer 4 · answered Nov 14 '12 at 16:32

Maybe a bit complicated but it works for what you need:

declare @test table(mytext varchar(50))

insert @test values('Washington 40 New Orleans 32')
insert @test values('Detroit 27 St. Louis 23')

select 
WinningTeam=SubString(mytext, 0,PatIndex('%[0-9.-]%', mytext)), 
WinningTeamScore=Left(SubString(mytext, PatIndex('%[0-9.-]%', mytext), 50),PatIndex('%[^0-9.-]%', SubString(mytext, PatIndex('%[0-9.-]%', mytext), 50) + 'X')-1),
LosingTeam=SubString(mytext, PatIndex('%[0-9.-]%', mytext)+3,PatIndex('%[0-9.-]%', mytext)), 
LosingTeamScore=reverse(Left(SubString(reverse(mytext), PatIndex('%[0-9.-]%', reverse(mytext)), 50),PatIndex('%[^0-9.-]%', SubString(reverse(mytext), PatIndex('%[0-9.-]%', reverse(mytext)), 50) + 'X')-1)) 
from @test

The query above works for scores under 100 points but you can modify it to deal with any number.

This is almost perfect. I just had to remove the "." from the pattern. So [0-9.-] was changed to [0-9-]. It works. Thanks a lot David!!!! — codingguy3000, Nov 14 '12 at 16:45

score 3 · Answer 5 · answered Nov 18 '14 at 22:46

3

my query for selecting multi-didget numbers out of a url (ignore rows without a number and rows where the number is after the '?'

select 
    URL,
    substring(URL,PATINDEX ('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',URL),7) as id
from data 
where 
    PATINDEX ('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',URL)>0 
and PATINDEX ('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',URL)<charindex ('?',URL)

answered Nov 18 '14 at 22:46

Andrew Hill

1,921
1
25
31

1

Can you please add example of input and output strings of your query? – Przemyslaw Remin Dec 21 '17 at 16:44

score 0 · Answer 6 · answered Dec 09 '22 at 03:25

WITH CTE AS (
    SELECT 'Washington 40 New Orleans 32' as RawData
    UNION ALL
    SELECT 'Detroit 27 St. Louis 23'
), CTEGetFirsTeam as ( --Find first score and everything before it is first team name, everything after is second team
    SELECT SUBSTRING(c.RawData, 1, c.pos1Start - 1) as WinningTeam
         , CONVERT(int, SUBSTRING(c.RawData, c.pos1Start + 1, c.pos1End - c.pos1Start - 1)) as WinningTeamScore
         , SUBSTRING(c.RawData, c.pos1End + 1, LEN(c.RawData)) as SecondTeam
    FROM (
        SELECT b.*,
               pos1End = b.pos1Start + PATINDEX('% %', b.NewData)
        FROM ( 
            SELECT a.*, SUBSTRING(a.RawData, a.pos1Start + 1, LEN(a.RawData)) as NewData
            FROM (
                SELECT RawData, pos1Start = PATINDEX('% [0-9]%', RawData)
                FROM CTE
                ) a
            ) b
        ) c
) --Repeat for second team
SELECT c.WinningTeam, c.WinningTeamScore
    , SUBSTRING(c.RawData, 1, pos1Start - 1) as LosingTeam
    , CONVERT(int, SUBSTRING(c.RawData, pos1Start + 1, pos1End - pos1Start - 1)) as LosingTeamScore
FROM (
    SELECT b.*,
        pos1End = LEN(b.RawData) + 1
    FROM (
        SELECT a.*, SUBSTRING(a.RawData, a.pos1Start + 1, LEN(a.RawData)) as NewData
        FROM (
            SELECT WinningTeam, WinningTeamScore, SecondTeam as RawData, pos1Start = PATINDEX('% [0-9]%', SecondTeam)
            FROM CTEGetFirsTeam 
            ) a
        ) b
    ) c`

In SQL Server can you find the first number in a string?

6 Answers6

Linked