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.
[sql]SELECT SUBSTRING(‘YesNo’, 4 – 3 * columnName, 3) as YesNo FROM TableName[/sql]
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.
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.
@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.
I would think using a CASE statement would be much more efficient.
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…
Neat trick. I know you probably meant to say SUBSTRING(’YesNo’, 1, 3) returns YES but you said returns ‘NO’.
Well spotted Boyan. Thanks. I have updated it now.
For oracle:
select decode(YesNoCol, 1, ‘Yes’, ‘No’) from TableName;
Wow this is a very clever trick.
Try this:
SELECT IF(columnName, ‘Yes’, ‘No’) as YesNo FROM TableName
@Marek, I dont think IF is a Sql server function. Looks like a Mysql function to me.
A very nice and clever trick. It helped me to update my strings.