convert string to integer data type

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

SQL Server 2005 Forums

Transact-SQL (2005)

convert string to integer data type

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-05 : 00:15:02

hi
i try convert char to int data type it shows the error

select * from tblsizemaster where fldsizedesc < 23

error message: Conversion failed when converting the varchar value '23.5' to data type int.

Desikankannan

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-05 : 00:23:02

Post the table structure with sample values..

Balaji.K

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-05 : 00:27:31

hi,
try this one

declare @tab table ( id int,name varchar(20))
insert into @tab (id,name)
select 1,'23.5' union all
select 2,'34.5' union all
select 3,'45.56'

select * from @tab where CAST(cast(name AS NUMERIC(18,2))as int)>23

Thanks,
vikky

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-05 : 00:47:43

Hi,

my table structure is
table name tblsizemaster

fldsizecode varchar(8)
fldsizedesc varchar(50)

i tried bhavani solution
select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-05 : 01:03:15

SELECT *
FROM tblsizemaster
WHERE CONVERT(DECIMAL(10,2), fldsizedesc) < 23

Try this..

Balaji.K

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-05 : 01:39:43

quote:
Originally posted by kbhere

SELECT *
FROM tblsizemaster
WHERE CONVERT(DECIMAL(10,2), fldsizedesc) < 23

Try this..

Balaji.K

hi,
try this one

declare @tab table ( id int,name varchar(20))
insert into @tab (id,name)
select 1,'23.5' union all
select 2,'34.5' union all
select 3,'45.56'

select * from @tab where CAST(cast(name AS NUMERIC(18,2))as int)>23

Thanks,
Bhavani. -- now vikky

Hi,

my table structure is
table name tblsizemaster

fldsizecode varchar(8)
fldsizedesc varchar(50)

i tried bhavani solution
select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

Try this...

DECLARE @TAB TABLE ( ID INT,NAME VARCHAR(20))

INSERT INTO @TAB (ID,NAME)
SELECT 1,'23.5' UNION ALL
SELECT 2,'34.5' UNION ALL
SELECT 3,'45.56'

SELECT *
FROM @TAB
where CAST(NAME AS NUMERIC(38, 0)) > 23

OR


DECLARE @TAB TABLE ( ID INT,NAME VARCHAR(20))

INSERT INTO @TAB (ID,NAME)
SELECT 1,'23.5' UNION ALL 
SELECT 2,'34.5' UNION ALL 
SELECT 3,'45.56'


SELECT * 
FROM @TAB 
WHERE ISNUMERIC(NAME)<23

-------------------------
R...

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-05 : 02:03:50

hi

the query is working

select * from tblsizemaster where convert(numeric(18,3),fldsizedesc) < 23

but again its shows the error message

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

Desikankannan

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-07 : 01:14:53

Still i got problem

my table tblsizemaster

fields
fldsizecode varchar(8)
fldsizedesc varchar(50)

select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-07 : 01:41:36

Declare @tblsizemaster table(fldsizecode varchar(8),
fldsizedesc varchar(50))

insert into @tblsizemaster values(21,25.4)
insert into @tblsizemaster values(22,27.4)
insert into @tblsizemaster values(25,28.4)

select * from @tblsizemaster where cast(fldsizedesc as float)>26.5

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 02:52:00

quote:
Originally posted by desikankannan

Hi,

my table structure is
table name tblsizemaster

fldsizecode varchar(8)
fldsizedesc varchar(50)

i tried bhavani solution
select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

Seems you have bad data ie some alphanumeric data

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 03:00:47

quote:
Originally posted by rajdaksha

quote:
Originally posted by kbhere

SELECT *
FROM tblsizemaster
WHERE CONVERT(DECIMAL(10,2), fldsizedesc) < 23

Try this..

Balaji.K

hi,
try this one

declare @tab table ( id int,name varchar(20))
insert into @tab (id,name)
select 1,'23.5' union all
select 2,'34.5' union all
select 3,'45.56'

select * from @tab where CAST(cast(name AS NUMERIC(18,2))as int)>23

Thanks,
Bhavani. -- now vikky

Hi,

my table structure is
table name tblsizemaster

fldsizecode varchar(8)
fldsizedesc varchar(50)

i tried bhavani solution
select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

Try this...

DECLARE @TAB TABLE ( ID INT,NAME VARCHAR(20))

INSERT INTO @TAB (ID,NAME)
SELECT 1,'23.5' UNION ALL
SELECT 2,'34.5' UNION ALL
SELECT 3,'45.56'

SELECT *
FROM @TAB
where CAST(NAME AS NUMERIC(38, 0)) > 23

OR

DECLARE @TAB TABLE ( ID INT,NAME VARCHAR(20))

INSERT INTO @TAB (ID,NAME)
SELECT 1,'23.5' UNION ALL
SELECT 2,'34.5' UNION ALL
SELECT 3,'45.56'

SELECT *
FROM @TAB
WHERE ISNUMERIC(NAME)<23

-------------------------
R...

Isnumeric() is not reliable
See here
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-07 : 03:11:58

Hi

Yes after i have done workaround that Isnumeric().

Thanks Madhi...

-------------------------
R...

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-07 : 03:47:48

Hi madivanan

working well and thanks

quote:
Originally posted by madhivanan

quote:
Originally posted by desikankannan

Hi,

my table structure is
table name tblsizemaster

fldsizecode varchar(8)
fldsizedesc varchar(50)

i tried bhavani solution
select * from tblsizemaster where CAST(cast(fldsizedesc AS NUMERIC(18,2))as int)>23.5

its not working

error message
Error converting data type varchar to numeric.

Desikankannan

Seems you have bad data ie some alphanumeric data

Madhivanan

Failing to plan is Planning to fail

Desikankannan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources