CAST problem
-
I have a table with a field that is supposed to be used only for numbers, but the DBA set it up as varchar. So naturally if I pull data and try to compare that field to a number, explicitly or implicitly, I get a syntax error if there are any letters in that field(which there are and I can't clean them)
What I'd like is a View that ignores those junk rows and returns the field as an int. I've tried SELECT CAST(field1 as int) as field1 ....WHERE isnumeric(field1) =1, but still I get the syntax error if I try to compare that field to and int.
Anyone have any ideas?
-
As far as explicitly goes, you can wrap your int with quotes, but that's probably not the best solution. Or cast your ints to varchars and then compare. I don't like the solution, but it would probably work.
-
I am not sure why you are getting an error. Try like this.
CREATE TABLE MyVarchar
(
MyField VARCHAR(10)
)
INSERT MyVarchar
SELECT 'A123' UNION
SELECT '123' UNION
SELECT 'B234' UNION
SELECT '234' UNION
SELECT 'C345' UNION
SELECT '345' UNION
SELECT '345345E3' UNION
SELECT '234e2' UNION
SELECT '234x34'
SELECT * FROM MyVarchar
GO
CREATE VIEW vw_MyNumeric
AS
SELECT CONVERT(INT, MyField) MyField
FROM
MyVarchar
WHERE
MyField NOT LIKE '%.%'
AND MyField NOT LIKE '%e%'
AND MyField NOT LIKE '%d%'
AND ISNUMERIC(MyField) = 1
GO
SELECT *
FROM
vw_MyNumeric
WHERE
MyField = 123
GO
DECLARE @myIntTbl TABLE
(
IntVal INT
)
INSERT @myIntTbl
SELECT 123 UNION
SELECT 678
SELECT *
FROM
vw_MyNumeric
JOIN
@myIntTbl
ON
MyField = IntVal
GO
DROP VIEW vw_MyNumeric
GO
DROP TABLE MyVarchar
GO
Regards,
gova -
ISNUMERIC will fail due to the fact that there are things other than 0-9 that are valid for the function since it also checks for float, decimal and money types. Someone who posts here regularly has a neat little bit of SQL code that does what you are looking for and I thought I had it bookmarked but can't find it. Do a search on ISNUMERIC and you should find it. (Or maybe the originator of that piece of code will post it again...)
-
Unfortunately isnumeric is NOT reliable for your case
You may want to use LIKE or patindex instead
* Noel -
This was bugging me so I did some searching.
Here's more than you ever wanted to know about the shortcomings of ISNUMERIC:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=1
(with a link on the first page to even more discussion)
Here's a link to a script that will do what you want:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=897
-
Select case when dbo.IsInt(ColName) = 1 then CAST(ColName as int) else NULL end, ...
FROM TableName
Where dbo.IsInt(ColName) = 1
_____________
Code for TallyGenerator -
To summarize your request for a view to simply reject those things where "somecolumn" has things in it besides numeric digits...
CREATE VIEW dbo.someviewname
AS
SELECT somecolumns --Could use a "*" here to act like a "synonym"
FROM sometable
WHERE somecolumn NOT LIKE '%[^0-9]%'
GO
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply