concat and remove last char in string

  • Hi

    I need to create a number of strings depending on bool-values in a number of fields. I want the output to be like "petrol/diesel" if the column petrol and diesel is true. Any number of columns may be true or false and none may also be true.

    input columns:

    petrol

    diesel

    E85

    battery

    possible outputs:

    petrol (petrol=true, other=false)

    petrol/E85 (petrol, E85=true, other=false)

    diesel/battery (diesel, battery=true, other=false)

    [empty] (all false)

    The query below works with one exception - it does not remove the trailing '/'. The problem was that when using left(..., 1) afterwords it crashed when string was empty.

    SELECT

    FuelType = LEFT(CASE petrol WHEN 1 THEN 'petrol/' ELSE '' END +

    CASE diesel WHEN 1 THEN 'Diesel/' ELSE '' END +

    CASE E85 WHEN 1 THEN 'E85/' ELSE '' END +

    CASE battery WHEN 1 THEN 'battery/' ELSE '' END

    , LEN(CASE petrol WHEN 1 THEN 'petrol/' ELSE '' END +

    CASE diesel WHEN 1 THEN 'Diesel/' ELSE '' END +

    CASE E85 WHEN 1 THEN 'E85/' ELSE '' END +

    CASE battery WHEN 1 THEN 'battery/' ELSE '' END) - 0) Should be a one but don't work when empty

    FROM CarTable

    Would be nice to be able to use a variable to avoid all this repeating of statements, but I can't figure out how.

    Björn

  • Easier to remove a leading '/'

    SELECT

    FuelType = SUBSTRING(

    CASE petrol WHEN 1 THEN '/petrol' ELSE '' END +

    CASE diesel WHEN 1 THEN '/Diesel' ELSE '' END +

    CASE E85 WHEN 1 THEN '/E85' ELSE '' END +

    CASE battery WHEN 1 THEN '/battery' ELSE '' END

    ,2,8000)

    FROM CarTable

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks for that brilliant and simple solution!

    Björn

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply