Select satement with cast - 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)

Select satement with cast

Author

Topic

jamie_sql
Starting Member

3 Posts

Posted - 2007-09-26 : 16:50:16

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

30421 Posts

Posted - 2007-09-26 : 16:52:39

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"

sshelper
Posting Yak Master

216 Posts

Posted - 2007-09-26 : 16:53:19

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

jamie_sql
Starting Member

3 Posts

Posted - 2007-09-26 : 17:05:59

Thank you very much I will try them and let you know how it goes.

jamie_sql
Starting Member

3 Posts

Posted - 2007-09-26 : 17:27:11

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 17:29:04

ISNUMERIC will tell you that

1E-02
1D3
.

are "numeric" values...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 17:30:43

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 17:33:55

Values like

-4040.03$
£1

are also evaluated as numeric with isnumeric() function.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 02:23:07

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 02:25:54

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

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:36:26

"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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 09:40:16

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 09:43:59

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"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:50:20

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:47:34

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"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 11:26:42

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:14:43

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:20:41

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

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 02:56:17

[code]
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
[/code]
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

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 02:57:28

[code]
SELECT ColumnValue
from #TEMP
WHERE
ISNUMERIC(ColumnValue+'e0') = 1 and
CAST(ColumnValue AS INT) <= 1000000
[/code]
Same thing, but its fine with the sub-select creating the data

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 03:06:13

So it behaves differently in SQL Server 2005?
Strange

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources