Case statement 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

General SQL Server Forums

New to SQL Server Programming

Case statement help

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-11-05 : 13:39:35

select * into #t1
from pCodes

update #t1
set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1
where len(postcode) = 6

update #t1
set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1
where len(postcode) = 7

whats wrong with my case statment below cant seem to get it working!

SELECT
CASE POSTCODE WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
CASE POSTCODE WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
END
FROM #T1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 13:42:22

quote:
Originally posted by jaskalirai

select * into #t1
from pCodes

update #t1
set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1
where len(postcode) = 6

update #t1
set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1
where len(postcode) = 7

whats wrong with my case statment below cant seem to get it working!

SELECT
CASE ~~POSTCODE~~ WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3)
~~CASE POSTCODE~~ WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3)
END as NewCol
FROM #T1

Notice I also removed commas at the end of some lines...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 13:43:44

You are missing an END. Check BOL for the syntax.


CASE
  WHEN ... THEN ...
  WHEN ... THEN ...
  ELSE ...
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jaskalirai
Starting Member

31 Posts

Posted - 2007-11-05 : 14:15:56

thank you ever so much !!! how long do you think it will take me to get properly familiar with sql? is there any tips you can give me ?

quote:
Originally posted by dinakar

quote:
Originally posted by jaskalirai

select * into #t1
from pCodes

update #t1
set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1
where len(postcode) = 6

update #t1
set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1
where len(postcode) = 7

whats wrong with my case statment below cant seem to get it working!

SELECT
CASE ~~POSTCODE~~ WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3)
~~CASE POSTCODE~~ WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3)
END as NewCol
FROM #T1

Notice I also removed commas at the end of some lines...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 14:21:30

quote:
Originally posted by jaskalirai

thank you ever so much !!! how long do you think it will take me to get properly familiar with sql? is there any tips you can give me ?

I'm still working on getting better at it. It took years to get this far though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 00:51:46

<<
thank you ever so much !!! how long do you think it will take me to get properly familiar with sql? is there any tips you can give me ?
>>

Over the period of time

You should always clearly read the examples given in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources