query help - 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

SQL Server Development (2000)

query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2008-01-03 : 17:09:56

[code]
I have written a query which zero's are truncating in the results

select testid =
(
Case When testid = 1234 then '001'
When Testid = 1324 then '002'
When Testid = 1224 then '003'
else testid end)

from table

I got the below results

Testid
------
1
2
3

Where as i need the results

Testid
-----
001
002
003

Thanks for your help in advance
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-03 : 17:24:01

That is expected as TestID is an Integer, correct? Try this:

select testid =
(
Case When testid = 1234 then '001'
     When Testid = 1324 then '002'
     When Testid = 1224 then '003'
else CAST(testid AS VARCHAR(8))  end)

from table

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-03 : 17:33:50

[code]
select
testid =
case
when testid = 1234 then '001'
when Testid = 1324 then '002'
when Testid = 1224 then '003'
else right('000'+convert(varchar(20),testid),3)
end
from
table
[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 08:29:40

quote:

Originally posted by sqldba2k6


I have written a query which zero's are truncating in the results

select testid =
(
Case When testid = 1234 then '001'
     When Testid = 1324 then '002'
     When Testid = 1224 then '003'
else testid  end)

from table

I got the below results

Testid
------
1
2
3

Where as i need the results 

Testid
-----
001
002
003

Thanks for your help in advance

If testid's value is 25678 then is this your expected result?

Testid
-----
001
002
003
25678

Madhivanan

Failing to plan is Planning to fail

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2008-01-04 : 10:24:50

[code]
Thanks it works..

I want to get one more results with my query..
Can i write in better way....
select testid =
(
Case When testid = 1234 then '001'
When testid = 1234 then testid1='02'
When Testid = 1324 then '002'
When testid = 1324 then testid1='03'
When Testid = 1224 then '003'
When Testid = 1224 then '06'
else testid end)

from table

I want the ouput like below.

Testid Testid1
------ -------
001 02
002 03
003 06

[/code]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2008-01-04 : 10:24:51

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-04 : 12:06:12

you need two case expressions to do that. one for the Testid column, and another one for the Testid1 column.

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-07 : 00:55:35

quote:

Originally posted by sqldba2k6


Thanks it works..

I want to get one more results with my query..
Can i write in better way....
select testid =
(
Case When testid = 1234 then '001'
     When testid = 1234 then testid1='02' 
     When Testid = 1324 then '002'
     When testid = 1324 then testid1='03' 
     When Testid = 1224 then '003'
     When Testid = 1224 then '06'
else testid  end)

from table

I want the ouput like below.

Testid    Testid1
------    -------
001        02
002        03  
003        06

Did you read my previous post?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources