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
6条答案
按热度按时间s1ag04yj1#
Is there a way to identify the position of the first number in a string?
Yes
PATINDEX
returns 0 if the pattern can't be found or the1
based index of the beginning of the match otherwise.z18hc3ub2#
You can use the PATINDEX function instead of CHARINDEX, here lies the documentation ;)
wqlqzqxt3#
Here is a very ugly implementation of
PATINDEX()
which returns the data in the multiple columns:See SQL Fiddle with Demo
qq24tv8q4#
Maybe a bit complicated but it works for what you need:
The query above works for scores under 100 points but you can modify it to deal with any number.
tzdcorbm5#
选择multi-did的查询从url中获取数字(忽略没有数字的行和数字在“?”后面的行
iyfjxgzm6#