Cast varchar as int SQL statement

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?
 

Sgraffite

Supreme [H]ardness
Joined
Jan 10, 2006
Messages
4,407
I think it is because INT is ambiguous, try:
Code:
SELECT  cast(column1 as unsigned)
FROM database;
 

heatlesssun

Extremely [H]
Joined
Nov 5, 2005
Messages
44,154
There's no type checking going on there so I think that some of the column1 rows simply can't convert.
 

PTNL

Supreme [H]ardness
Joined
Jan 2, 2005
Messages
4,199
^^^

Does "column1" allow NULLs? If so, you'll need to coalesce the data.
 

texuspete00

Supreme [H]ardness
Joined
Sep 9, 2002
Messages
5,608
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:

Sgraffite

Supreme [H]ardness
Joined
Jan 10, 2006
Messages
4,407
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.
 

Sgraffite

Supreme [H]ardness
Joined
Jan 10, 2006
Messages
4,407
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
 

ameoba

Supreme [H]ardness
Joined
Jan 9, 2001
Messages
6,413
Fix the front-end so that it actually puts numbers in the DB. Scrub the data once. Keep the DB interaction simple.
 

mikeblas

[H]ard|DCer of the Month - May 2006
Joined
Jun 26, 2004
Messages
12,776
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.
 

Banko

Gawd
Joined
Jul 9, 2004
Messages
991
microsoft sql 2008

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.
 

Wiseguy2001

2[H]4U
Joined
Nov 28, 2001
Messages
3,470
... i know i get the obvious, but i need a temporary fix

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).
 

dalearyous

[H]ard|Gawd
Joined
Jun 21, 2008
Messages
1,922
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.
 

dalearyous

[H]ard|Gawd
Joined
Jun 21, 2008
Messages
1,922
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

this worked! although it isn't writing the changes so i need to make few slight changes
 
Top