SQL how create a Boolean column based on the value of another column

Question

I have an sql query that retrieves a column which values are strings. I want to create a column next to it that takes a value of 1 if the substring 'MB' is contained in the value or 0 otherwise

Could you provide your code..

Sunny S.M
– Sunny S.M

2015-09-23 09:27:40 +00:00
Commented Sep 23, 2015 at 9:27 — Sunny S.M
– Sunny S.M, Commented Sep 23, 2015 at 9:27

Rahul Tripathi · Accepted Answer · 2015-09-23 09:32:51Z

3

You can try it using the case when then like this:

select case when INSTR('mycol', 'MB') > 0 then 1 
       else 0 
       end as myBoolCol

edited Sep 23, 2015 at 9:32

answered Sep 23, 2015 at 9:29

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

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

2 Comments

René Hoffmann Over a year ago

How would one know pos and len?

Rahul Tripathi Over a year ago

@RenéHoffmann:- Updated the answer. However I am not sure how OP is extracting the 'MB' string.

René Hoffmann · Accepted Answer · 2015-09-23 09:38:02Z

2

You can output another column with a calculated value like this:

select COLUMN, IF(LOCATE('MB', COLUMN) > 0, 1, 0) as STR_FOUND
  from TABLE

See the documentation of locate and if for further Information.

edited Sep 23, 2015 at 9:38

answered Sep 23, 2015 at 9:30

René Hoffmann

2,8252 gold badges23 silver badges45 bronze badges

2 Comments

Nikhil Vartak Over a year ago

I don't see a LOCATE function in MSSQL

Rahul Tripathi Over a year ago

This is not MSSQL. It is MySql

Nikhil Vartak · Accepted Answer · 2015-09-23 09:41:16Z

1

select column, 
       case when column like '%mb%' then 1
       else 0 end 
from table

This works for both MSSQL and MYSQL

answered Sep 23, 2015 at 9:41

Nikhil Vartak

5,1473 gold badges29 silver badges36 bronze badges

Collectives™ on Stack Overflow

SQL how create a Boolean column based on the value of another column

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related