MikeM670
asked on
Display multiple fields with separator.
I have three fields that define the relationship a person has to a particular incident.
table Fields:
Field1
Field2
Field3
MS SQL
What I want to do is display the values in these fields with a '-' separating them. Not all fields need to be filled in so I don't want the display to show extra '-'
example:
Family - Brother - Younger /// When all three fields are filled in
Family - Brother- /// When only first two fields are filled in. Trailing '-' should be removed.
Family -- /// When only first field is filled in. Trailing '-'s should be removed.
- Brother - Younger /// When only fields two and three are filled in. (There currently is no restriction on leaving the first field blank and using the other two but its not
recommended, So in this case I would want the first '-' removed and only display Brother - Younger.
What would be the best way to go about doing this. I can use this in several other record types so should this be a sp or function?
table Fields:
Field1
Field2
Field3
MS SQL
What I want to do is display the values in these fields with a '-' separating them. Not all fields need to be filled in so I don't want the display to show extra '-'
example:
Family - Brother - Younger /// When all three fields are filled in
Family - Brother- /// When only first two fields are filled in. Trailing '-' should be removed.
Family -- /// When only first field is filled in. Trailing '-'s should be removed.
- Brother - Younger /// When only fields two and three are filled in. (There currently is no restriction on leaving the first field blank and using the other two but its not
recommended, So in this case I would want the first '-' removed and only display Brother - Younger.
What would be the best way to go about doing this. I can use this in several other record types so should this be a sp or function?
lcohan
I would use a SELECT....CASE....WHEN....
ASKER
I've been messing with Cancat() and Cancat_WS(). I would think there would be a way to use them to achieve what I'm looking for.
Are you having any issues with concat_ws? It should behave exactly like you're talking about:
concat_ws(' - ', Field1, Field2, Field3)
And only ever leave a single '-', neither trailing or leading.
But - depending on what you mean by "not filled in". The concat will effectively skip fields if they're null - if they're empty strings they'll still be included.
concat_ws(' - ', Field1, Field2, Field3)
And only ever leave a single '-', neither trailing or leading.
But - depending on what you mean by "not filled in". The concat will effectively skip fields if they're null - if they're empty strings they'll still be included.
ASKER
I'm afraid that maybe the case of the field not being empty. The user can select a value and then change the field from empty to some value. They also can remove the value and that would leave a '' value in the field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Icohan using case was my first thought but it would get complicated.
Snarf0001 Cancat_WS just needed the nullif() to make it work properly.
Snarf0001 Cancat_WS just needed the nullif() to make it work properly.