Conversion Error - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Conversion Error

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-01 : 08:31:43

select 'Unit_Number' = case
when isnumeric(unit_number) = 1 then unit_number
when isnumeric(unit_number) = 0 then cast(unit_number as int)
end
from MyTable

The select statement gives error, can anybody tell me why is this giving error?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 08:39:28

What is the error you got?
Instead of isnumeric, use Like '%[0-9]%'

select 'Unit_Number' = case
when unit_number like '%[0-9]%' then unit_number
else cast(unit_number as int) end from MyTable

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-01 : 08:50:27

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

I actually want to cast this column into int for sorting purpose. Because this column can contain both values like 1,2,3,4,5 and >1000, I want to display all numeric values in an order.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 08:56:34

If unit_number is varchar datatype then
Try this

Select unit_number as 'unit_number' from yourTable order by len(unit_number), unit_number

Otherwise,Post the table structure, some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-01 : 09:05:09

when we use len(unit_number) its not acceptable when using DISTINCT. What is the problem with ISNUMERIC?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 09:08:53

I repeat what I asked you
Post the table structure, some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-01 : 09:27:35

I want the output in order
declare @Test table (Unit_Number varchar(255))
insert into @Test
select 1 union all
select 10 union all
select 999 union all
select 2 union all
select 3 union all
select 4 union all
select 11 union all
select 100 union all
select 101 union all
select 1000 union all
select '>0001' union all
select '>0002'
select * from @Test

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 09:34:15

Well
What is the output you want from those?

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-01 : 09:58:23

1
2
3
4
10
11
100
101
999
1000

while the >0001 and 2 should be below

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 10:08:47

Thats what I suggested already

declare @Test table (Unit_Number varchar(255))
insert into @Test
select 1 union all
select 10 union all
select 999 union all
select 2 union all
select 3 union all
select 4 union all
select 11 union all
select 100 union all
select 101 union all
select 1000 union all
select '>0001' union all
select '>0002'

select * from @Test order by len(Unit_Number),Unit_Number

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-02 : 01:08:03

execute this

declare @Test table (Unit_Number varchar(255))
insert into @Test
select 1 union all
select 10 union all
select 10 union all
select 999 union all
select 2 union all
select 3 union all
select 4 union all
select 11 union all
select 100 union all
select 101 union all
select 1000 union all
select '>0001' union all
select '>0002'

select distinct unit_number from @Test order by len(Unit_Number),Unit_Number

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-02 : 01:14:19

Let me understand this....you want to sort characters ('>0001' & '>0002') and numerics together?

Couldn't you just normalize your data? What prevents you from converting the column of figures by CAST? You mentioned it earlier.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 01:32:49

Here is a query

Select * from (
Select distinct unit_number from @test
) T order by len(unit_number),unit_number

But everytime you give only part of result and not exact one
Whenever you ask questions, post your full requirement clearly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources