Merge String into single string

Question

Post reply

Merge String into single string

sabarishbabu

Old Hand

Points: 371
More actions
December 1, 2022 at 4:28 pm

#4120744
Hello Team,
I have a input fields like below with fixed value separator (6) and the expected output like below, Please advice the same .
Thanks,
- This topic was modified 1 year, 11 months ago by sabarishbabu.

December 4, 2022 at 6:32 pm

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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

sabarishbabu Old Hand Points: 371 More actions · Answer 2

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,

This reply was modified 1 year, 11 months ago by sabarishbabu.
This reply was modified 1 year, 11 months ago by sabarishbabu.

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

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?

MarkP Old Hand Points: 305 More actions · Answer 7

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