question

anandgu avatar image
anandgu asked

How do I write multiple case statements in one single statement?

case WHEN len(price_dt)>25 THEN SUBSTRING(price_dt,1,10) WHEN len(price_dt)=13 THEN REPLACE(price_dt,'.','') WHEN len(price_dt)=11 THEN REPLACE(price_dt,'**','') --WHEN price_dt like '%est' THEN cast(convert(varchar,cast( replace( price_dt,'est','') as datetime), 112) as datetime) WHEN price_dt like '%st%' THEN cast(convert(varchar,cast( replace( price_dt,'st','') as datetime), 112) as datetime) WHEN price_dt like '%nd%' THEN cast(convert(varchar,cast( replace( price_dt,'nd','') as datetime), 112) as datetime) WHEN price_dt like '%rd%' THEN cast(convert(varchar,cast( replace( price_dt,'rd','') as datetime), 112) as datetime) WHEN price_dt like '%th%' THEN cast(convert(varchar,cast( replace( price_dt,'th','') as datetime), 112) as datetime) --WHEN len(price_dt)=19 THEN SUBSTRING(REPLACE(REPLACE(price_dt,'00:00:00',''),' ','/') ,1,(LEN(REPLACE(REPLACE(price_dt,'00:00:00',''),' ','/') )-1))
sql-server-2008sql-server-2008-r2sql-server-2012performance-tuning
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
As an aside, CASE...WHEN...THEN...ELSE...END is an expression, not a statement.
3 Likes 3 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
what do you mean by one single statement?
0 Likes 0 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Yes didnt get the question, your statement looks all right, just add else condition and then end as column name
0 Likes 0 ·
anandgu avatar image anandgu commented ·
Thanks a lot guys
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
`+1` @GPO I had resisted commenting that myself :)
0 Likes 0 ·
Show more comments
Gazz avatar image
Gazz answered
That is what a CASE expression looks like (although you forgot the END at the end of yours) [ https://msdn.microsoft.com/en-gb/library/ms181765.aspx][1] Also you may want to stick an ELSE on the end of it just to catch anything that the WHENs don't [1]: https://msdn.microsoft.com/en-gb/library/ms181765.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image
Gazz answered
I assume those WHENs in the middle are getting rid of the letters on a date i.e 1st, 2nd, 3rd, 4th if you wanted to put it in a single statement you could do nested REPLACE, but what you have looks fine. (also this would give you a different output to what you are currently getting if you had something like "1strd" so it) cast(convert(varchar,cast( replace( replace( replace( replace( price_dt,'est',''),'st',''),'nd',''),'rd','') ,'th','') as datetime), 112) as datetime)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.