In Sql Server Can You Find The First Number In A String?

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 d

Solution 1:

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.

Solution 2:

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

Solution 3:

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

Solution 4:

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

declare@testtable(mytext varchar(50))

insert@testvalues('Washington 40 New Orleans 32')
insert@testvalues('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.

Solution 5:

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 
wherePATINDEX ('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',URL)>0 
andPATINDEX ('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',URL)<charindex ('?',URL)