Conditional entries in CONCAT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Conditional entries in CONCAT

Author

Topic

MJW
Starting Member

2 Posts

Posted - 2015-02-04 : 06:38:54

I would like to concatenate multiple fields together and am currently using the code below.

SELECT
Product,
Client_ID,
Policy_ID,
Sequence,
Plan_ID,
CONCAT (
FUND_01 ,'~', INV_PERC_01 ,'~',
FUND_02 ,'~', INV_PERC_02 ,'~',
FUND_03 ,'~', INV_PERC_03 ,'~',
FUND_04 ,'~', INV_PERC_04 ,'~',
FUND_05 ,'~', INV_PERC_05 ,'~',
FUND_06 ,'~', INV_PERC_06 ,'~',
FUND_07 ,'~', INV_PERC_07 ,'~',
FUND_08 ,'~', INV_PERC_08 ,'~',
FUND_09 ,'~', INV_PERC_09 ,'~',
FUND_10 ,'~', INV_PERC_10 ,'~')
From #tmp1

The problem I have is not everyone has 10 funds - so where a client is not using a fund I am just getting a long line of tildes after the real values, like below

Fund1~50.00~Fund2~50.00~~~~~~~~~~~~~

I would like to buld the CONCAT so that I can conditionally exclude fields where there is no fund / percent (I have NULLs in the fund field where it is not used) so the extra tildes do not show, like this
Fund1~50.00~Fund2~50.00

Is there any way to do this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-04 : 09:26:37

REPLACE(CONCAT(...), '~~', '')

jjourneyy22
Starting Member

3 Posts

Posted - 2015-02-09 : 02:01:10

unspammed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-09 : 03:48:40

SELECT Product, Client_ID, Policy_ID, Sequence, Plan_ID,
stuff(CONCAT ('~' + FUND_01 ,'~' + INV_PERC_01 , '~' + FUND_02 ,'~'+ INV_PERC_02 ,'~'+FUND_03 ,'~'+ INV_PERC_03 ,'~'+FUND_04 ,'~'+ INV_PERC_04 ,'~'+FUND_05 ,'~'+INV_PERC_05 ,'~'+FUND_06 ,'~'+ INV_PERC_06 ,'~'+FUND_07 ,'~'+INV_PERC_07 ,'~'+FUND_08 ,'~'+INV_PERC_08 ,'~'+FUND_09 ,'~'+ INV_PERC_09 ,'~'+FUND_10 ,'~'+INV_PERC_10), 1, 1, '')
From #tmp1

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MJW
Starting Member

2 Posts

Posted - 2015-02-09 : 04:07:04

Thanks for the suggestions - i'll try them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources