SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select satement with cast
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jamie_sql
Starting Member

3 Posts

Posted - 09/26/2007 :  16:50:16  Show Profile  Reply with Quote
Hello all. Sorry I am a little new to sql and was looking for some advice. I am trying do a select and a cast. I have a field that is a char(12). I need to find all values over 1000000 but there is data like 23433 and data like 'STAFF'. I tried the below statement but when it runs into something like 'STAFF' it can not convert. I have tried to convert to varchar as well and it does not work :(

select CAST(column_name AS int) as 'PositionNum' from table_name where column_name is <= 1000000

Any help would be GREAT!!!

Thanks,

Jamie

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/26/2007 :  16:52:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select column_name from (
select column_name from table_name where column_name not like '%[^0-9]%'
) AS d where cast(column_name as int) >= 1000000



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 09/26/2007 :  16:53:19  Show Profile  Visit sshelper's Homepage  Reply with Quote
Try using the ISNUMERIC function to determine if the column value is numeric:

SELECT * FROM Table_Name
WHERE ISNUMERIC(Column_Name) = 1 AND CAST(Column_Name AS INT) <= 1000000

SQL Server Helper
http://www.sql-server-helper.com
Go to Top of Page

jamie_sql
Starting Member

3 Posts

Posted - 09/26/2007 :  17:05:59  Show Profile  Reply with Quote
Thank you very much I will try them and let you know how it goes.
Go to Top of Page

jamie_sql
Starting Member

3 Posts

Posted - 09/26/2007 :  17:27:11  Show Profile  Reply with Quote
Thank you both. I ended up using sshelper just because it seemed like I could understand it more. I understand how it works I just don't see how it gets the ISNUMERIC before it does the cast. Very interesting.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/26/2007 :  17:29:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ISNUMERIC will tell you that

1E-02
1D3
.

are "numeric" values...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/26/2007 :  17:30:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Even if you don't understand a solution right away, take your time to investigate and learn.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/26/2007 :  17:33:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Values like

-4040.03$
£1

are also evaluated as numeric with isnumeric() function.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/27/2007 :  02:23:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sshelper

Try using the ISNUMERIC function to determine if the column value is numeric:

SELECT * FROM Table_Name
WHERE ISNUMERIC(Column_Name) = 1 AND CAST(Column_Name AS INT) <= 1000000

SQL Server Helper
http://www.sql-server-helper.com


Your query wont work for all combinations of data


SELECT * FROM 
(
	select 'staff' as Column_Name union all
	select '234000' as Column_Name union all
	select '12d1' as Column_Name union all
	select '45e0' as Column_Name union all
	select '$12,345' as Column_Name 
)
as Table_Name
WHERE ISNUMERIC(Column_Name) = 1 AND CAST(Column_Name AS INT) <= 1000000


Error

Server: Msg 245, Level 16, State 1, Line 2

Syntax error converting the varchar value '12d1' to a column of data type int.


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/27/2007 :  02:25:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jamie_sql

Hello all. Sorry I am a little new to sql and was looking for some advice. I am trying do a select and a cast. I have a field that is a char(12). I need to find all values over 1000000 but there is data like 23433 and data like 'STAFF'. I tried the below statement but when it runs into something like 'STAFF' it can not convert. I have tried to convert to varchar as well and it does not work :(

select CAST(column_name AS int) as 'PositionNum' from table_name where column_name is <= 1000000

Any help would be GREAT!!!

Thanks,

Jamie


You should always use proper datatype to store the data

You should use Peso's query which is the way to go as ISNUMERIC() is not always reliable

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  09:36:26  Show Profile  Reply with Quote
"Try using the ISNUMERIC function to determine if the column value is numeric:

SELECT * FROM Table_Name
WHERE ISNUMERIC(Column_Name) = 1 AND CAST(Column_Name AS INT) <= 1000000
"

I've had instances where this approach hasn't worked (with IsDate() as it happens) - the Set Based stuff evaluating non linearly left-to-right I suppose, in which cases I have used a nested select to force the evaluation order:

SELECT * 
FROM 
(
    SELECT *
    FROM Table_Name
    WHERE ISNUMERIC(Column_Name) = 1
) AS X
WHERE CAST(Column_Name AS INT) <= 1000000

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/27/2007 :  09:40:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Still doesn't work Kristen. Neither on SQL Server 2000 nor SQL Server 2005.
SELECT	Column_Name
from	(
		SELECT	Column_Name
		FROM	(
				select 'staff' as Column_Name union all
				select '234000' as Column_Name union all
				select '12d1' as Column_Name union all
				select '45e0' as Column_Name union all
				select '$123.45' as Column_Name union all
				select '$12,345' as Column_Name 
			) as Table_Name
		WHERE	ISNUMERIC(Column_Name) = 1 
	) as d
where	CAST(Column_Name AS INT) <= 1000000


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 09/27/2007 09:47:10
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/27/2007 :  09:43:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here is a real shocker!

Run this code as is
SELECT	Column_Name
from	(
		SELECT	Column_Name
		FROM	(
				select 'staff' as Column_Name union all
				select '234000' union all
				select '12d1' union all
				select '45e0' union all
				select '$123.45' union all
				select '$12,345'
			) as Table_Name
		where column_name not like '%[^0-9]%'
	) as d
--where	CAST(Column_Name AS INT) <= 1000000
Now activate the second where and run again.

On SQL Server 2000 the code work.
On SQL Server 2005 the code do not work.

It THIS a bug, or feature?


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 09/27/2007 09:47:40
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  09:50:20  Show Profile  Reply with Quote
Blimey mate, that's scary.

I tried putting the data in a #TEMP table first, in case the optimiser was over-doing it with the in-place UNION ALL, but it gave the same result.

And a

SELECT	Column_Name
FROM	#TEMP
where column_name not like '%[^0-9]%'

definitely only returns the one good row

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/27/2007 :  10:47:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This, however creepy as it seems, work on both SQL Server 2000 and SQL Server 2005
SELECT	d.ColumnValue
FROM	(
		SELECT	'staff' AS ColumnValue UNION ALL
		SELECT	'234000' UNION ALL
		SELECT	'12d1' UNION ALL
		SELECT	'45e0' UNION ALL
		SELECT	'$123.45' UNION ALL
		SELECT	'$12,345'
	) AS d
WHERE	d.ColumnValue NOT LIKE '%[^0-9]%'
	AND CAST(d.ColumnValue AS INT) <= 1000000
Shifting order for the two WHERE's make the query fail, which has some meaning that SQL Server short-circuits the evaluations.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  11:26:42  Show Profile  Reply with Quote
Doesn't seem to work with my #TEMP table:

SELECT	Column_Name
from	#TEMP
where	    column_name not like '%[^0-9]%' 
	AND CAST(Column_Name AS INT) <= 1000000


Server: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'staff' to data type int.

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/28/2007 :  02:14:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Doesn't seem to work with my #TEMP table:

SELECT	Column_Name
from	#TEMP
where	    column_name not like '%[^0-9]%' 
	AND CAST(Column_Name AS INT) <= 1000000


Server: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'staff' to data type int.

Kristen


Why?

SELECT	* into #temp
FROM	(
		SELECT	'staff' AS ColumnValue UNION ALL
		SELECT	'234000' UNION ALL
		SELECT	'12d1' UNION ALL
		SELECT	'45e0' UNION ALL
		SELECT	'$123.45' UNION ALL
		SELECT	'$12,345'
	) AS d


SELECT	ColumnValue
from	#TEMP
where	    ColumnValue not like '%[^0-9]%' 
	AND CAST(ColumnValue AS INT) <= 1000000

drop table #temp


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/28/2007 :  02:20:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

"Try using the ISNUMERIC function to determine if the column value is numeric:

SELECT * FROM Table_Name
WHERE ISNUMERIC(Column_Name) = 1 AND CAST(Column_Name AS INT) <= 1000000
"

I've had instances where this approach hasn't worked (with IsDate() as it happens) - the Set Based stuff evaluating non linearly left-to-right I suppose, in which cases I have used a nested select to force the evaluation order:

SELECT * 
FROM 
(
    SELECT *
    FROM Table_Name
    WHERE ISNUMERIC(Column_Name) = 1
) AS X
WHERE CAST(Column_Name AS INT) <= 1000000

Kristen


No need of nested select as long as ISNUMERIC() is not reliable
Force ISNUMERIC() to be reliable

SELECT	ColumnValue
FROM	(
		SELECT	'staff' AS ColumnValue UNION ALL
		SELECT	'234000' UNION ALL
		SELECT	'12d1' UNION ALL
		SELECT	'45e0' UNION ALL
		SELECT	'$123.45' UNION ALL
		SELECT	'$12,345'
	) AS d
WHERE 
	ISNUMERIC(ColumnValue+'e0') = 1	and 
	CAST(ColumnValue AS INT) <= 1000000



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2007 :  02:56:17  Show Profile  Reply with Quote

SELECT	* into #temp
FROM	(
		SELECT	'staff' AS ColumnValue UNION ALL
		SELECT	'234000' UNION ALL
		SELECT	'12d1' UNION ALL
		SELECT	'45e0' UNION ALL
		SELECT	'$123.45' UNION ALL
		SELECT	'$12,345'
	) AS d


SELECT	ColumnValue
from	#TEMP
where	    ColumnValue not like '%[^0-9]%' 
	AND CAST(ColumnValue AS INT) <= 1000000

drop table #temp

gives

Server: Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value 'staff' to data type int.

on SQL 2005 here

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2007 :  02:57:28  Show Profile  Reply with Quote

SELECT	ColumnValue
from	#TEMP
WHERE 
	ISNUMERIC(ColumnValue+'e0') = 1	and 
	CAST(ColumnValue AS INT) <= 1000000

Same thing, but its fine with the sub-select creating the data

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/28/2007 :  03:06:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
So it behaves differently in SQL Server 2005?
Strange

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000