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

---

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

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

---

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

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

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

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

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

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

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

---

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

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

---

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

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

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