Cast varchar as int SQL statement

D

dalearyous

[H]ard|Gawd
Joined
Jun 21, 2008
Messages
1,922
why doesn't this work?

Code: 
SELECT  cast(column1 as int)
FROM database;

just to explain, there is a value that the user is supposed to fill in with number, not characters but every now and then some idiot does fill it in with characters and it breaks a bunch of sorting scripts.

in my example, column1 is varchar(15). most of you will want to reply "just change it so it doesn't accept characters or just change it from varchar to int in the table structure. well, short answer is i can't. so i just want to cast or convert any characters in that column to int.

i feel like my query should work but it fails to convert to int. thoughts?
 
I think it is because INT is ambiguous, try:
Code: 
SELECT  cast(column1 as unsigned)
FROM database;
 
There's no type checking going on there so I think that some of the column1 rows simply can't convert.
 
^^^

Does "column1" allow NULLs? If so, you'll need to coalesce the data.
 
You can block out anything that isn't a number.

Code: 
SELECT 
	CASE 
		WHEN Field NOT LIKE '%[^0-9]%'
		THEN CAST(Field AS INT)
		ELSE NULL 
	END

Only thing is, a value like "5,000" will fail. This makes sure every character is comprised of 0 to 9 (It would accept leading zeroes). If you tuned the LIKE, you can't allow anything that will crash the CAST. I think somehow, you're going to need to test your value before casting it.
 
Last edited:
MySQL doesn't seem to care if the column allows NULLs when casting to unsigned, but we're still not sure which database type the OP is using.
 
This should work unless the number CAST is too large to fit into int:
Code: 
SELECT
	CASE
		WHEN column1 IS NULL OR column1 NOT LIKE '%[0-9]%' THEN 0
		ELSE CAST(column1 AS int)
	END AS column1

Basically a combo of PTNL and texuspete00's suggestions
 
Fix the front-end so that it actually puts numbers in the DB. Scrub the data once. Keep the DB interaction simple.
 
You're getting the error because you're trying to cast a value that isn't an integer. ameoba is right; if you want to store integers, store integers.
 
dalearyous said:
microsoft sql 2008
Click to expand...

If you are using sql server just try convert(int,column) instead.

Also if some of the data actually has letters it will fail. The most you can do is maybe something like convert(int,ascii(columnName)) but I'm not sure if that will work.

Though that would give you a very weird representation.
 
dalearyous said:
... i know i get the obvious, but i need a temporary fix
Click to expand...

What they said is an easy fix (create a new int column, copy/convert the values, remove the old one, then rename it). If you leave stupid stuff like this in there it WILL come back haunt you!

A better way to do this is to use the SQL server Import and Export Data Tool to copy the all the database tables into the correct formats/ constraints. This makes life much easier if you are taking over a project with an 'iffy' database (plus it will import data from virtually any format).
 
oh sorry, thanks for the reply and fix!

what i mean when i said i know i get the obvious was ... whoever created the DB structure never should have made it a varchar to begin with and it must be changed.
 
Sgraffite said:
This should work unless the number CAST is too large to fit into int:
Code: 
SELECT
	CASE
		WHEN column1 IS NULL OR column1 NOT LIKE '%[0-9]%' THEN 0
		ELSE CAST(column1 AS int)
	END AS column1

Basically a combo of PTNL and texuspete00's suggestions
Click to expand...

this worked! although it isn't writing the changes so i need to make few slight changes
 
You must log in or register to reply here.
Back
Top