SQL Server - convert to integer for comparison

[Spudhead]

OK, I've got a field in that should by rights be integer, but due to over-zealous muppetry, is varchar. Mostly, the data is integers. Sometimes, it's "N/A" or some other horrible character string.

I want to pass a value from a form, via the WHERE clause of my SQL string; the form value is ALWAYS an integer. Like:
strSQL=strSQL&" AND U_SleepAdultUp2 >="&accomodates

This obviously works fine until it comes up against "N/A" or its friends. I get "Error converting varchar to int" or whatever.

Similarly, I can't use:
strSQL=strSQL&" AND CONVERT(U_SleepAdultUp2 AS Integer) >="&accomodates

For the same reason.

What I want is an IF statement in the SQL string - I think - that says "If the value in field 'U_SleepAdultUp2' is, or can be converted to, an integer, then return the row if that value is greater than or equal to [whatever value I've just sent in the SQL string], otherwise ignore it and stop annoying me."

Help :(

[raf]

Not an expert on stored procedures, but since you can use if then structures in them, is uppose you could solve this by writing a stored procedure and executing that from your asp script
some more info here
http://www.4guysfromrolla.com/webtech/010600-1.2.shtml

I suppose fixing thisat the db-level by transforming the variable is out of the question? (--> that's what i'd do. numerical variabels are faster searched then string variabels)
By the way, dus >= + a value without quotes works at all?

[allida77]

You can try a IsNumeric() which will return a 1 if it is a number and 0 if not. I think that since the column is a varchar then "24234" will still be a varchar. You can not put a If/Else in your SQL but you you might be able to use a CASE

Select ivar = CASE IsNumeric(U_SleepAdultUp2) WHEN 1 THEN
CONVERT(U_SleepAdultUp2 AS Integer) END FROM table WHERE U_SleepAdultUp2) <> "N/A"

I am not sure if that is correct CASE syntax for ms sql. You might want to just try:
SELECT IsNumeric(U_SleepAdultUp2) FROM table

If this returns all 0s then you will not be able to do this through SQL IMO.

You could to this in a sp but you would need to trap the error number and then

If @@Error = (can not convert to int)
then do this sql ...

If all else fails then you could do this in your asp code.





http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_syntaxc_9kvn.asp

[Spudhead]

Cheers guys :)

allida77 - I like the CASE idea, might work with that but my problem with the WHERE is that I can't just do:

U_SleepAdultUp2) <> "N/A"

as I can't be certain that other string values won't get shoved in there (oh, the joys of letting users directly at your database :rolleyes: )

A stored proc seems likely, but, daft as this sounds, I'm a bit limited in my freedom to mess around with the database server. The IT support muppets are likely to view with suspicion a mere developer asking to have "procedures" "stored" on there.

Anyway, I've handed it to my boss - he's the one that decided to let them put strings in there in the first place, he can fix his own mess :thumbsup:

[raf]

I found a real Dilbert sollution so your boss will probably love this :D (i request 50% of ypour promotion ):D

First create a new table with one variable and record -->
variable = "nonnumeric"
value = "N/A"

Then, change your select, to make a join with this table, on your current variabe and this new variable with operator = '<>'.
Like
sql="select * FROM originaltable INNER JOIN newtable ON originaltable.originalvariable <> nawtable.newvariable WHERE ... AND U_SleepAdultUp2 >="&accomodates

No? If this doesn't work, then you nee to use a subquery with the join in, and have the where clause after it. Like

sql="select * FROM originaltable WHERE primarykeyvariable IN(SELECT primarykeyvariable FROM originaltable INNER JOIN newtable ON originaltable.originalvariable <> nawtable.newvariable) WHERE ... AND U_SleepAdultUp2 >="&accomodates

Might need some experimenting but i should be possible. (First create the view with the unmatched records on the stringvalues and then run the select with your condition)

[Spudhead]

I like it :) I'll pass it on to boss dude...

[raf]

As long as i get me 50% ... :)

Anyway, if you'd try it, make sure that this new variable in the new table is of the same variabletype as the variable you're querying it with. And of course, you could have more then 1 record (= stringvalue) in this table.

+ before inserting new records, check if the value is numeric in ASP to avoid other stuf like 5ooo getting in your table. (by the looks of it, this tabl was originally an imported Excel file with empty cells). Whammy posted a nice regex for that here some weeks ago.

[whammy]

Easier answer, CAST the varchar as an integer...

"SELECT CAST(fieldname AS INTEGER) AS booyah..."

Now you can compare booyah as an INTEGER.

[raf]

CAST, Humm.

Just curious, in what integer will "N/A" be CASTed?

[whammy]

It won't... it will throw an error...

[raf]

Perhaps something like

I get "Error converting varchar to int" or whatever.

--> quote from spudheads initial post + problems that needed to be solved ;)

[whammy]

So, I'd make sure that the value is an INT first (especially since it's a value submitted by a form, that's easy... just make sure that varBlah = CInt(varBlah)).

[raf]

:confused: Quote from my post you initially responded to.
+ before inserting new records, check if the value is numeric in ASP to avoid other stuf like 5ooo getting in your table. (by the looks of it, this tabl was originally an imported Excel file with empty cells). Whammy posted a nice regex for that here some weeks ago. ;)
The problem seems to be that these non-numerical values are allready inside the table.
I should probably let this slide, but the problem is bugging me. I don't see an easier way to filter out the records with a non-numerical value, then to run a find-unmatched query and then run the select on the resulting view (or by using a stored procedure)

[Spudhead]

OK, an update.

Raf, I like your solution but unfortunately due to power politics and over-zealous protection of frankly rather pants code architecture, it's not something I can implement. If we did it on one site, we'd have to do it on all of them; it's not our jobs worth, etc etc etc. Sorry :rolleyes:

So - we've "agreed" that the only non-numeric values in that column will be "N/A" and "Not on file"; which means, of course, that I can do:


strSQL=strSQL&" AND (U_SleepAdultUp2<>'N/A' AND U_SleepAdultUp2<>'Not on file') AND CAST(U_SleepAdultUp2 AS Integer)>="&accomodates


...which seems to work. Which muppet gets to go through the database looking for anomolous entries, and what happens when said muppet forgets and starts putting "Don't know", "Unavailable" and "Fish" in there, is not my concern. :cool:

[raf]

It's your (or your bosses) call, but everyone knows it will weight on performance + will get you into trouble some day.

Ah well. What's better then being able to learn from your compagnies mistakes while being payed, so you can avoid making these mistakes when you write something for yourself?

[Roy Sinclair]

Use IsNumeric in your WHERE clause:

select cast(badfieldtype as integer) as goodfieldtype where IsNumeric(badfieldtype) = 1