Reputation: 6103
This is how I retrieve data
select myPlan from myTable
this is result ,
myPlan
=====================
True , False , True
False , True , False
False , True , True
In each row , I have three Boolean value separated by comma .
The first Boolean value represent Breakfast , the second is Lunch and the last is Dinner .
I just want to replace the result to
myPlan
=====================
BreakFast , Dinner
Lunch
Lunch , Dinner
Can I make this replacement by using only sql query
?
I'm using SQL server 2008 R2
!
Upvotes: 1
Views: 251
Reputation: 2744
A shorter solution:
select
decode(substr(myPlan,0,1),'T','Breakfast',null) || decode(substr(myPlan,0,1),'T',' , ',null) ||
decode(substr(myPlan,9,1),'T','Lunch',null) || decode(substr(myPlan,9,1),'T',' , ',null) ||
decode(substr(myPlan,17,1),'T','Dinner',null)
from mytable;
Upvotes: 1
Reputation: 1750
You can use string functions as follows.
DECLARE @TEMP AS TABLE(MyPlan VARCHAR(100))
INSERT INTO @TEMP(MyPlan) VALUES ('True , False , True')
INSERT INTO @TEMP(MyPlan) VALUES ('False , True , False')
INSERT INTO @TEMP(MyPlan) VALUES ('False , True , True')
SELECT
CASE
WHEN RTRIM(SUBSTRING(MyPlan,1,5)) = 'TRUE' THEN 'BREAKFAST'
ELSE ''
END
+CASE
WHEN RTRIM(SUBSTRING(MyPlan,9,5)) = 'TRUE' THEN ',LUNCH'
ELSE ''
END
+CASE
WHEN RTRIM(SUBSTRING(MyPlan,17,5)) = 'TRUE' THEN ',DINNER'
ELSE ''
END
FROM @TEMP
Upvotes: 1
Reputation: 32402
Since you have just 3 booleans you could use a case statement for all 8 possibilities.
SELECT
(CASE
WHEN myPlan = 'True , True , True'
THEN 'Breakfast, Lunch, Dinner'
WHEN myPlan = 'True , True , False'
THEN 'Breakfast, Lunch'
WHEN myPlan = 'True , False , False'
THEN 'Breakfast'
WHEN myPlan = 'True , False , True'
THEN 'Breakfast, Dinner'
WHEN myPlan = 'False , False , True'
THEN 'Dinner'
WHEN myPlan = 'False , True , True'
THEN 'Lunch, Dinner'
WHEN myPlan = 'False , True , False'
THEN 'Lunch'
ELSE ''
END) myPlan
FROM myTable
Upvotes: 4