converting a number to char in a query

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

SQL Server Development (2000)

converting a number to char in a query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-10 : 06:36:30

i have a select which bring a column with value 1 to 4
and i want to make something like :
select '0'+col1......
i tried even:

select cast('0'+col1 as nvarchar(2)) as aaa ......
but nothing gives me '01' or '02' .....
how can i do this"thanks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-10 : 07:02:12

ok i did '0'+cast(col1 as nvarchar(2))
in the above the cade should have been
"select cast('0'+col1 as nvarchar(1)) as aaa ......"
the size of the nvarchar(2)id the problem

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pootle_flump

1064 Posts

Posted - 2006-04-10 : 07:02:18

Hi

Sadly SQL Server performs an implicit conversion rather than throw an error - the error would give you a good clue. You need to convert your number. If it will always be 1, 2, 3 or 4 then easiest would be:
'0' + CAST(col1 AS Char(1))

HTH

pootle_flump

1064 Posts

Posted - 2006-04-10 : 07:02:47

Lol - must type faster

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 07:28:14

Why do you want to concatenate number with strings?

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-10 : 07:52:28

beacuse for example this numbers tell me the status of a bank account 1 to 4
and when i need to send it to a DOS system it demans a 2 lettes status so i must do '01'...

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 08:44:33

If you use front end application use the formations there

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-04-10 : 09:23:39

You should probably force it to the number of characters rather than blindly adding a zero...

Something like:

aaa = Right('00' + convert(varchar,col1),2)
or
aaa = Right(Replicate('0',2) + convert(varchar,col1),2)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 09:33:22

How did you forget Stuff corey?

declare @t int
set @t=1
select replace(str(@t,2),' ','0')
select stuff('0'+cast(@t as char(1)),1,1,'0')

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-04-10 : 09:37:33

I didn't forget it

, but I don't think 'stuff' is appropriate. That could incorrectly overwrite information... though not in this case since the numbers are 1-4.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources