Post reply

Concatenating with conditional delimiter

  • May 10, 2010 at 1:57 pm

    #386896

    I'm struggling to find a graceful way to concatenate 3 string values from a record, separated by dashes? Any or all of the 3 could be null, in which case dash should not be included. Data should look like:

    one-two-three

    one-two

    one-three

    two-three

    two

    NOT

    one--three

    -two-three

    --three

    one-two-

    Sample data set

    declare @tbl table(field1 varchar(5), field2 varchar(5), field3 varchar(5))

    insert into @tbl (field1, field2, field3) values ('one', 'two', 'three')

    insert into @tbl (field1, field3) values ('one', 'three')

    insert into @tbl (field1) values ('one')

    insert into @tbl (field2) values ('two')

    insert into @tbl (field3) values ('three')

    insert into @tbl (field1, field2) values ('one', 'two')

    insert into @tbl (field2, field3) values ('two', 'three')

  • May 10, 2010 at 2:14 pm

    #1165225

    I am sure there are other ways as well, but here is one way:

    DECLARE @tbl TABLE(field1 VARCHAR(5), field2 VARCHAR(5), field3 VARCHAR(5))

    INSERT INTO @tbl (field1, field2, field3) VALUES ('one', 'two', 'three')

    INSERT INTO @tbl (field1, field3) VALUES ('one', 'three')

    INSERT INTO @tbl (field1) VALUES ('one')

    INSERT INTO @tbl (field2) VALUES ('two')

    INSERT INTO @tbl (field3) VALUES ('three')

    INSERT INTO @tbl (field1, field2) VALUES ('one', 'two')

    INSERT INTO @tbl (field2, field3) VALUES ('two', 'three')

    SELECT SUBSTRING(

    COALESCE(field1 + '-', '')

    + COALESCE(field2 + '-', '')

    + COALESCE(field3 + '-', '')

    , 1, LEN(

    COALESCE(field1 + '-', '')

    + COALESCE(field2 + '-', '')

    + COALESCE(field3 + '-', ''))-1)

    FROM @tbl

    -- Cory

  • May 10, 2010 at 2:26 pm

    #1165229

    Here's a slightly different apporach (using the sample data Cory set up):

    I prefer STUFF over SUBSTRING for scenarios like this since it's a little less coding and it doesn't include the addtl. LEN() function.

    SELECT

    STUFF(

    COALESCE('-'+field1 , '')

    + COALESCE('-'+field2 , '')

    + COALESCE('-'+field3 , '')

    , 1,1,'')

    FROM @tbl


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 10, 2010 at 2:32 pm

    #1165231

    I really need to start using Stuff more - I have been one-upped twice in a week using stuff over substring. 😀

    Thanks, I am going to really try and remember this!

    -- Cory

  • May 10, 2010 at 2:34 pm

    #1165234

    Thanks to both of you, much more elegant than the grunt code I had come up with.

  • May 10, 2010 at 2:42 pm

    #1165241

    Glad I could help 😀


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 10, 2010 at 6:34 pm

    #1165285

    Cory E. (5/10/2010)

    I am sure there are other ways as well, but here is one way:

    DECLARE @tbl TABLE(field1 VARCHAR(5), field2 VARCHAR(5), field3 VARCHAR(5))

    INSERT INTO @tbl (field1, field2, field3) VALUES ('one', 'two', 'three')

    INSERT INTO @tbl (field1, field3) VALUES ('one', 'three')

    INSERT INTO @tbl (field1) VALUES ('one')

    INSERT INTO @tbl (field2) VALUES ('two')

    INSERT INTO @tbl (field3) VALUES ('three')

    INSERT INTO @tbl (field1, field2) VALUES ('one', 'two')

    INSERT INTO @tbl (field2, field3) VALUES ('two', 'three')

    SELECT SUBSTRING(

    COALESCE(field1 + '-', '')

    + COALESCE(field2 + '-', '')

    + COALESCE(field3 + '-', '')

    , 1, LEN(

    COALESCE(field1 + '-', '')

    + COALESCE(field2 + '-', '')

    + COALESCE(field3 + '-', ''))-1)

    FROM @tbl

    STUFF works fine but let's demonstrate a little trick that a lot of people don't know about with SUBSTRING... you don't actually have to calculate a length for the 3rd operand...

    SELECT SUBSTRING(

    COALESCE('-'+ field1, '')

    + COALESCE('-'+ field2, '')

    + COALESCE('-'+ field3, '')

    ,2,8000)

    FROM @tbl

    From Books Online (Substring)...

    length

    Is a positive integer that specifies how many characters or [font="Arial Black"]bytes of the expression will be returned[/font]. If length is negative, an error is returned. length can be of type bigint.

    Notice what is says... "bytes of the expression". If the number of bytes specified in the 3rd operand of SUBSTRING exceed the number of bytes in the expression, it's not going to add any extra bytes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 11, 2010 at 5:40 am

    #1165467

    Jeff Moden (5/10/2010)

    STUFF works fine but let's demonstrate a little trick that a lot of people don't know about with SUBSTRING... you don't actually have to calculate a length for the 3rd operand...

    Thanks! I had often wondered that, but never verified. I should have looked more at it.

    -- Cory

  • May 11, 2010 at 6:20 am

    #1165488

    Cory E. (5/11/2010)

    Jeff Moden (5/10/2010)

    STUFF works fine but let's demonstrate a little trick that a lot of people don't know about with SUBSTRING... you don't actually have to calculate a length for the 3rd operand...

    Thanks! I had often wondered that, but never verified. I should have looked more at it.

    Heh... thanks for the feedback and not a problem.

    I use the SubString trick a lot for "complicated" splits that aren't done (for some reason) by the GUI. You're just not as "lazy" as I am... I hate typing code to do LEN calculations in SubStrings. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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