converting problem - 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)

converting problem

Author

Topic

hybridGik
Starting Member

26 Posts

Posted - 2006-06-28 : 19:26:40

Hi!

I'm having problem with the converting/casting thing of the data type. I'm using SQL Server 2000 and the error is this:
-------------------------------------------------------------
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A2' to a column of data type int.
-------------------------------------------------------------

and here is my query:
------------------------------------------------------------------------
select case when len(cast(ordernumber as varchar)) > 2 then (case(case when len(cast(ordernumber as varchar)) > 2 then substring(cast(ordernumber as varchar), 1, 2)
end
)
when 53 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'A')
when 54 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'B')
when 55 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'C')
when 56 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'D')
when 57 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'E')
when 58 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'F')
when 59 then replace(cast(ordernumber as varchar), substring(cast(ordernumber as varchar), 1, 2), 'G')
end
)
else OrderNumber
end
from tblOrders
----------------------------------------------------------------------------

I hope someone could help me.

Thanks in advanced.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 19:35:40

Please explain what you are trying to do, show what the data in the tblOrders table looks like, and the output you are trying to get.

There is likely a much simpler way to do this if we know what your are trying to do.

CODO ERGO SUM

hybridGik
Starting Member

26 Posts

Posted - 2006-06-28 : 19:45:55

I have a field/column in tblOrders named "OrderNumber"

These are the data in the column:

OrderNumber
-----------
532
532
533
542
545
555
564
566

What I want to do is replace the first 2 characters with a letter.

So 532 would be A2, 542 would be B2 and so on.

this query will be incorporated to the "then" part of a case statement which is in the stored procedure i have.

It works when i change the letter to a number. But i need that to be a letter.

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 20:04:21

This should do it.


select
	[New Order Number]=
	case substring(convert(varchar(20),a.OrderNumber),1,2)
	when '53' then 'A'
	when '54' then 'B'
	when '55' then 'C'
	when '56' then 'D'
	when '57' then 'E'
	when '58' then 'F'
	when '59' then 'G'
	end+
	substring(convert(varchar(20),a.OrderNumber),3,20)
from
	tblOrders a

CODO ERGO SUM

hybridGik
Starting Member

26 Posts

Posted - 2006-06-28 : 20:12:34

here's the exact thing i have to do.

max length of the OrderNumber is suppose to be 6, so if the OrderNumber's length is more than 6, I have to replace the first 2 characters with a letter.

e.g.

Order Number -> Output
5314124 A14124
5422222 B22222
5533333 C33333

thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 20:34:36

After you convert the Order number, what will you do with it?

It looks like the Order Number is an integer or numeric, so you won't be able to put the new number into the Order Number Column.

CODO ERGO SUM

hybridGik
Starting Member

26 Posts

Posted - 2006-06-28 : 20:44:02

yes the ordernumber is an integer. but i dont need to store the value to the ordernumber. i need the value to use on the select statement.

like select 'whaterver_value_1111'

thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 20:51:20

If you have to do this a lot, you should write a function or view to do the conversion.

CODO ERGO SUM

hybridGik
Starting Member

26 Posts

Posted - 2006-06-28 : 22:59:34

no i won't use it alot. i'll just use it one time.

thanks.

hybridGik
Starting Member

26 Posts

Posted - 2006-06-29 : 11:28:05

here's a simpler query with a problem in convertig.

select case
when len(OrderNumber) > 2
then case when substring(cast(OrderNumber as varchar), 1, 2) = '53'
then replace(cast(OrderNumber as varchar), substring(cast(OrderNumber as varchar), 1, 2), 'A')
end
else OrderNumber
end
from tblOrders

What's the problem with it? Appreciate all your replies.

Thanks.

hybridGik
Starting Member

26 Posts

Posted - 2006-06-29 : 13:09:14

at last i found the problem.

the "else OrderNumber" part is where the problem occurs

it should be "else cast(OrderNumber as varchar)". hehehe.

problem solved.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources