SQL Version of YesNoFormat

On February 15, 2008, in SQL, by Anuj Gakhar

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.

Tagged with:  

11 Responses to SQL Version of YesNoFormat

  1. Fro says:

    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. Anuj Gakhar says:

    @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. I would think using a CASE statement would be much more efficient.

  4. Ron Stewart says:

    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. Neat trick. I know you probably meant to say SUBSTRING(’YesNo’, 1, 3) returns YES but you said returns ‘NO’.

  6. Anuj Gakhar says:

    Well spotted Boyan. Thanks. I have updated it now.

  7. Scott says:

    For oracle:

    select decode(YesNoCol, 1, ‘Yes’, ‘No’) from TableName;

  8. Çağlar Gülçehre says:

    Wow this is a very clever trick.

  9. Marek says:

    Try this:
    SELECT IF(columnName, ‘Yes’, ‘No’) as YesNo FROM TableName

  10. Anuj Gakhar says:

    @Marek, I dont think IF is a Sql server function. Looks like a Mysql function to me.

  11. Kolby Brient says:

    A very nice and clever trick. It helped me to update my strings.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar