select and if/else - 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)

select and if/else

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2008-10-24 : 12:53:57

Hi,

I have the following select statement -
SELECT staffno, name, lastname FROM emp

The 'staffno' is either be a 4 digit or a 5 digit INT.

In my select statement, i now need to make the 'staffno' a 7 digit number. So i need to put '120' before the 'staffno' if it is a 4 digit number...and '12' before the staffno if it is a 5 digit number.

I have been trying to do this with an if/else but am having trouble.

Anyone familar with if/else or a better way to achieve this.

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 13:01:55

[code]SELECT STUFF('120',8-LEN(staffno),LEN(staffno),staffno)*1, name, lastname FROM emp[/code]

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2008-10-24 : 13:14:13

cheers for that visakh16...

it works great for the 5 digit numbers...however for the 4 digit numbers it returns a null value.

kieran

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-24 : 13:19:26

select * from staff
SELECT
staffno=case
when len(staffno)=4
then cast(('120'+cast(staffno as varchar(7))) as int)
when len(staffno)=5
then cast(('12'+cast(staffno as varchar(7))) as int)
end,name,lastname
from emp

malay

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-24 : 13:23:02

***** not required -->SELECT * from staff

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 13:26:35

[code]SELECT STUFF('1200',8-LEN(staffno),LEN(staffno),staffno)*1, name, lastname FROM emp[/code]

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2008-10-24 : 13:32:44

Cheers visakh16...thats great...its working now!!! Thanks for ur help!!!

Cheers malay for ur help too...i like ur case example. However when i tried it out, i got the following error -
Line 2: Incorrect syntax near '='.
Out of curiousity, i wonder do u know whats wrong here as i had been trying to understand the 'case' syntax earlier and feel i can use this in the future.

kieran

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-24 : 13:37:33

I don't think it show error.

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 13:38:55

quote:
Originally posted by kieran5405

Cheers visakh16...thats great...its working now!!! Thanks for ur help!!!

Cheers malay for ur help too...i like ur case example. However when i tried it out, i got the following error -
Line 2: Incorrect syntax near '='.
Out of curiousity, i wonder do u know whats wrong here as i had been trying to understand the 'case' syntax earlier and feel i can use this in the future.

kieran

You're welcome

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2008-10-24 : 13:41:32

hi malay...i got the problem...the below returned the right staffno. But cheers for showing me this way too.

SELECT
case
when len(staffno)=4
then cast(('120'+cast(staffno as varchar(7))) as int)
when len(staffno)=5
then cast(('12'+cast(staffno as varchar(7))) as int)
end
from emp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources