Merge String into single string

  • Hello Team,

    I have a input fields like below with fixed value separator (6)  and the expected output like below, Please advice the same .

    Screenshot-1

    Thanks,

  • sabarishbabu wrote:

    Hello Team,

    I have a input fields like below with fixed value separator (6)  and the expected output like below, Please advice the same .

    Screenshot-1

    Thanks,

    Don't post graphics.  Folks can't copy and paste the data.  It's best to provide a simple CREATE TABLE and some code to populate the table.

    You posted in "General" and so I have to ask, which version of SQL Server are you using?  In this case, it can make a real difference.

     

    --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)

  • Hello Jeff,

    Sorry for that and got your point, I am using - Microsoft SQL Server 2017. Please fine the sample data in SQL format.

    SELECT '-1010-----' AS Field1,
    '2500------' AS Field2,
    '--111----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '2500------' AS Field1,
    '-1010-----' AS Field2,
    '--111----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '--111----' AS Field1,
    '2500------' AS Field2,
    '-1010-----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '2500------' AS Field1,
    '------' AS Field2,
    '------' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '------' AS Field1,
    '-2500-----' AS Field2,
    '------' AS Field3,
    '-2500-----' AS OutPut;

    Thanks,

  • Imo this is a guessing puzzle unless there's more of an explanation.  What are the rules to apply here?  Why not remove all of the dashes '-' and then concatenate the results according to the unknown but seemingly likely to exist rules?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • sabarishbabu wrote:

    Hello Jeff,

    Sorry for that and got your point, I am using - Microsoft SQL Server 2017. Please fine the sample data in SQL format.

    SELECT '-1010-----' AS Field1,
    '2500------' AS Field2,
    '--111----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '2500------' AS Field1,
    '-1010-----' AS Field2,
    '--111----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '--111----' AS Field1,
    '2500------' AS Field2,
    '-1010-----' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '2500------' AS Field1,
    '------' AS Field2,
    '------' AS Field3,
    '2500-1010-111----' AS OutPut
    UNION ALL
    SELECT '------' AS Field1,
    '-2500-----' AS Field2,
    '------' AS Field3,
    '-2500-----' AS OutPut;

    Thanks,

    What are all the dashes for?  Are you suggesting that the position in the string is based on how many leading dashes there are for each item?  If so, you have some seriously bad problems with the dashes.

    Are you also suggesting that you want partial fills to be made the same as full fills (that really seems like a very bad idez).

    --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)

  • Can you explain the rules? Looks something like

    field1x-field2x-field3x----

    where fieldnx = replace(fieldn,'-','')

    except your sample data desired output is all over the place

    a) the ordering of fields is inconsistent

    b) the number of hyphens at the start of the desired output is inconsistent

    c) the number of hyphens are the end of the desired output is inconsistent

    Maybe now you can see why no one is able to code this.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • SELECT '2500------' AS Field1,
    '------' AS Field2,
    '------' AS Field3,
    '2500-1010-111----' AS OutPut

    What are the rules? Where does "-1010-111----" come from for the output in that example?

  • This was removed by the editor as SPAM

  • Based on the image, it looks like the number of hyphens in front of the number determines the order of the numbers to be concatenated.

    The desired output looks to be

    FIRSTNUMBER-SECONDNUMBER-THIRDNUMBER----

    If SECONDNUMBER and/or THIRDNUMBER do not exist then NULL them.

    However, it looks like the last two lines in the union statement are totally different to the original example image. For that reason, I agree that there's nothing we can do until the rules are clarified

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

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