I was reading some SQL tips and found this small but quite handy tip. If you have a boolean column in your database which holds values like 0 and 1 , you can modify your Select queries to represent that data as a Yes or No by doing this.
SELECT SUBSTRING('YesNo', 4 - 3 * columnName, 3) as YesNo FROM TableName
The trick is if the column holds a value of 0, the SQL would read
SUBSTRING(‘YesNo’, 4, 3) which returns ‘No’
And if the Column has a value of 1, it becomes SUBSTRING(‘YesNo’, 1, 3) which returns ‘Yes’.
I like it. This was part of a bigger article found here.
#1 by Fro on February 16, 2008 - 7:16 am
Quote
That’s an interesting approach Anuj. Personally, I would use a CASE solution.
CASE columnname WHEN 1 THEN ‘Yes’ ELSE ‘No’ END AS ‘YesNo’
The primary reason I prefer this over your SUBSTRING method is that SQL Server doesn’t have to do any string parsing.
You probably won’t notice much of a difference with basic queries, but it could add up if you’re doing some heavy lifting.
#2 by Anuj Gakhar on February 16, 2008 - 11:24 am
Quote
@Fro, yes, that’s another way of doing it. I didnt think that String parsing was a time consuming thing in SQL. But I am not a SQL expert, so not really my domain.
Thanks for the note though.
#3 by Dan G. Switzer, II on February 16, 2008 - 2:46 pm
Quote
I would think using a CASE statement would be much more efficient.
#4 by Ron Stewart on February 16, 2008 - 3:57 pm
Quote
I’d use the CASE statement (and have for exactly this) because it would be much clearer to the next developer that comes along (which may well be me) and has to figure out what that clause within the query is doing…
#5 by Boyan Kostadinov on February 18, 2008 - 4:11 pm
Quote
Neat trick. I know you probably meant to say SUBSTRING(’YesNo’, 1, 3) returns YES but you said returns ‘NO’.
#6 by Anuj Gakhar on February 18, 2008 - 4:20 pm
Quote
Well spotted Boyan. Thanks. I have updated it now.
#7 by Scott on February 19, 2008 - 12:31 am
Quote
For oracle:
select decode(YesNoCol, 1, ‘Yes’, ‘No’) from TableName;
#8 by Çağlar Gülçehre on February 19, 2008 - 7:23 am
Quote
Wow this is a very clever trick.
#9 by Marek on February 25, 2008 - 12:13 pm
Quote
Try this:
SELECT IF(columnName, ‘Yes’, ‘No’) as YesNo FROM TableName
#10 by Anuj Gakhar on February 25, 2008 - 12:40 pm
Quote
@Marek, I dont think IF is a Sql server function. Looks like a Mysql function to me.