Replacing string in sql query

Question

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 !

It would be much simpler if you had a normalized database with 3 distinct boolean columns instead of one complex CHAR column ;) — Frazz
– Frazz, Commented May 15, 2014 at 5:32
@Frazz :) . I totally agree . I will obey that database normalization in next time . Thank you ! — zey
– zey, Commented May 15, 2014 at 6:30

FuzzyTree · Accepted Answer · 2014-05-15 05:42:36Z

4

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

answered May 15, 2014 at 5:42

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shree.pat18 Over a year ago

+1 because this seems to be quite reasonable given the table design!

Szymon Over a year ago

+1 but you should probably add the 9th option of 3xFalse

FuzzyTree Over a year ago

@Szymon 3xFalse is covered by ELSE ''

Allan S. Hansen Over a year ago

Only problem with this is that space formatting is important, but then again - the design is not ..... "optimal" to begin with :) So I'd propery do a replacement of spaces and comma with empty and then lower case it all so you get a 'truetruetrue' string for first case

zey Over a year ago

@AllanS.Hansen , I've remove spaces using Replace(myPlan, ' ', '') . Whatever , FuzzyTree's answer work perfectly . :)

Richard Vivian · Accepted Answer · 2014-05-15 06:04:34Z

1

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

edited May 15, 2014 at 6:04

answered May 15, 2014 at 5:47

Richard Vivian

1,7701 gold badge14 silver badges19 bronze badges

Comments

Arnab Bhagabati · Accepted Answer · 2014-05-15 06:37:03Z

1

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;

answered May 15, 2014 at 6:37

Arnab Bhagabati

2,7544 gold badges38 silver badges57 bronze badges

1 Comment

Richard Vivian Over a year ago

The OP uses MS SQL2008. decode is an Oracle function. (substr would be SUBSTRING in MS SQL

Collectives™ on Stack Overflow

Replacing string in sql query

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related