SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Char To Int Conv		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dim
Yak Posting Veteran

USA
57 Posts
Posted - 08/27/2010 :  16:33:13  Show Profile  Reply with Quote
Hi,

I have a column in the table defined with the datatype char(1) ....
the values that I have in the columns are
6
7

5

7
There are some blank in between. I need to convert this to datatype int .
The sql I use :

select
CASE WHEN column_a = '' THEN 0 ELSE cast(column_a as int) END
from table_a

The error that I get is as follows:


Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '.' to a column of data type int.

I need to convert this column to datatype int with blank values substituted with 0. I ned guidance /help in acheiving this. Please advice.

Thank You,
dim







Dp

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 08/27/2010 :  16:51:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Apparently your data also has a period in it. Have you considered using the IsNumeric function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dim
Yak Posting Veteran

USA
57 Posts
Posted - 08/27/2010 :  17:01:30  Show Profile  Reply with Quote
Hi tkizer,

Could you please help out as to how the syntax will look like if we want to eliminate the period using IsNumeric?

Thank You,
dim

Dp
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 08/27/2010 :  17:07:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
I haven't tested it, but maybe something like this would work:

select CASE WHEN IsNumeric(column_a) = 0 THEN 0 ELSE cast(column_a as int) END
from table_a

If that doesn't work, try this:
select cast(column_a as int) as column_a
from (select column_a = CASE WHEN IsNumeric(column_a) = 0 THEN '0' ELSE column_a END from table_a) dt

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 08/27/2010 :  17:30:31  Show Profile  Reply with Quote
A small variant of Tara's IsNumeric solution is to use a LIKE:
--Sample Data
DECLARE @T TABLE (column_a CHAR(1))

INSERT @T (column_a)
VALUES ('1'),
(' '),
('.'),
('$'),
('%'),
('^'),
('@'),
('3'),
('a'),
('4'),
('5'),
('7'),
('8'),
('-'),
('=')

SELECT
	CASE 
		WHEN column_a NOT LIKE '[0-9]' THEN 0
		ELSE CAST(column_a AS INT)
	END
FROM @T

-- OR
SELECT
	CASE 
		WHEN column_a LIKE '[^0-9]' THEN 0
		ELSE CAST(column_a AS INT)
	END
FROM @T
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000