Char To Int Conv - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Char To Int Conv

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-27 : 16:33:13

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

38200 Posts

Posted - 2010-08-27 : 16:51:27

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

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-27 : 17:01:30

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 17:07:38

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-27 : 17:30:31

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources