x

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)) 
more ▼

asked Jan 12, 2016 at 09:24 AM in Default

avatar image

anandgu
1 1 2

what do you mean by one single statement?

Jan 12, 2016 at 09:24 AM Kev Riley ♦♦

Yes didnt get the question, your statement looks all right, just add else condition and then end as column name

Jan 12, 2016 at 10:25 AM erlokeshsharma08

Thanks a lot guys

Jan 12, 2016 at 11:36 AM anandgu

As an aside, CASE...WHEN...THEN...ELSE...END is an expression, not a statement.

Jan 12, 2016 at 10:11 PM GPO

+1 @GPO I had resisted commenting that myself :)

Jan 13, 2016 at 09:14 AM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

Also you may want to stick an ELSE on the end of it just to catch anything that the WHENs don't

more ▼

answered Jan 12, 2016 at 09:28 AM

avatar image

Gazz
1k 3 11

(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Jan 12, 2016 at 10:54 AM

avatar image

Gazz
1k 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x788
x428
x75

asked: Jan 12, 2016 at 09:24 AM

Seen: 162 times

Last Updated: Jan 18, 2016 at 09:31 AM

Copyright 2018 Redgate Software. Privacy Policy