extract number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

extract number

Author

Topic

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 22:33:53

hi,

I have the following field in a column 'st_code'
t
23 - California : State

How do I extract just 23 out of it?

I tried LEFT(st_code, LEN(st_code)-CHARINDEX(':', st_code))

thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-16 : 22:46:29

LEFT(st_code, CHARINDEX(' ', st_code)) ??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-16 : 22:46:46

[code]select rtrim(left(st_code, charindex('-', st_code)-1)) as n
from table[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 23:13:20

thanks a ton

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:18:45

You might like to try:

LEFT(st_code, PATINDEX('%[^0-9]%', st_code)-1)

which will give you up to the first NON-numeric character. If you might have

23A - California : State
or
23-24 - California : State

then extend the range of valid characters:

LEFT(st_code, PATINDEX('%[^0-9A-Za-z\-]%', st_code)-1)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 02:28:39

Also read about parsename function in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources