Filtering out only number value in column

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

General SQL Server Forums

New to SQL Server Programming

Filtering out only number value in column

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-19 : 12:23:59

I have a column that look like this

September 13 US$ 1430.00 - September 14 US$ 1430.0
August 20 US$ 1700.00 - August 21 US$ 1700.00 - Au

Is there a way to return only

1430.00
1700.00

etc etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:30:07

if the format is consistent, you could try the below

SELECT SUBSTRING(Col,CHARINDEX('$',Col)+2,(CHARINDEX('-',Col)-1)-CHARINDEX('$',Col))
FROM YourTable

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-19 : 12:40:06

quote:
Originally posted by visakh16

if the format is consistent, you could try the below

SELECT SUBSTRING(Col,CHARINDEX('$',Col)+2,(CHARINDEX('-',Col)-1)-CHARINDEX('$',Col))
FROM YourTable

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-19 : 12:41:48

there are also rates that are normal in this column.

790.00
810.00
815.00

in addition to the ones that are messed up, maybe that has something to do with it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:43:53

ok. then alter it like this


SELECT SUBSTRING(Col,CASE WHEN CHARINDEX('$',Col)>0 THEN CHARINDEX('$',Col)+2 ELSE 1 END,CASE WHEN CHARINDEX('-',Col)>0 THEN (CHARINDEX('-',Col)-1)-CHARINDEX('$',Col) ELSE LEN(Col) END)
FROM YourTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:54:54

what all are possible formats of data in your column?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-19 : 13:01:27

quote:
Originally posted by visakh16

what all are possible formats of data in your column?

Was able to fix it, just had to alter some of the stuff in your select, thank you visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 13:04:07

quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

what all are possible formats of data in your column?

Was able to fix it, just had to alter some of the stuff in your select, thank you visakh!

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources