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 '/'
SELECTFuelType = 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