How to use conditional delimiters when using CONCAT

I need to concatenate three columns shown below using CONCAT. Some of the values in these columns can be NULL.

CREATION_DATE|NOTES|BLOCKED_DATE


SELECT        dbo.CAPABILITIES.SYCPC_AUTO_KEY, dbo.CAPABILITIES.PNM_AUTO_KEY, dbo.BLOCKED_PN.PN, dbo.BLOCKED_PN.CAP_AUTO_KEY, 
                         dbo.BLOCKED_PN.CREATION_DATE, dbo.BLOCKED_PN.NOTES, dbo.BLOCKED_PN.Blocked_date
FROM            dbo.CAPABILITIES RIGHT OUTER JOIN
                         dbo.BLOCKED_PN ON dbo.CAPABILITIES.CAP_AUTO_KEY = dbo.BLOCKED_PN.CAP_AUTO_KEY AND dbo.CAPABILITIES.PN = dbo.BLOCKED_PN.PN
WHERE        (dbo.CAPABILITIES.SYCPC_AUTO_KEY IS NOT NULL)
ORDER BY dbo.CAPABILITIES.SYCPC_AUTO_KEY

Open in new window

maximus1974Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Just pass the 3 columns as parameters into CONCAT() except for dates you will want to specify how they appear & if you have access to concat(), you also have access to FORMAT() which you can use like the following:

SELECT
      c.SYCPC_AUTO_KEY
    , c.PNM_AUTO_KEY
    , b.PN
    , b.CAP_AUTO_KEY
    , b.CREATION_DATE
    , b.NOTES
    , b.Blocked_date
    , CONCAT(format(b.CREATION_DATE,'yyyy-MM-dd'), b.NOTES, format(b.Blocked_date,'yyyy-MM-dd')) as cnb
FROM dbo.CAPABILITIES c
INNER JOIN dbo.BLOCKED_PN b ON c.CAP_AUTO_KEY = b.CAP_AUTO_KEY
      AND c.PN = b.PN
WHERE c.SYCPC_AUTO_KEY IS NOT NULL
ORDER BY
      c.SYCPC_AUTO_KEY

Open in new window

For CONCAT()  
All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string.

NB: There is no point to the RIGHT JOIN because you suppress that through your where clause condition, so, use a more efficient inner join instead. I would also suggest using table aliases.
Pawan KumarDatabase ExpertCommented:
The above code will NOT work as it has below issues-
1. The above code will never work as the spelling of CONCAT is written as CONACT. This looks like a typo.
2. Format(b.CREATION_DATE,'YYYY-MM-dd') is also incorrect. you will get YYYY-12-04 as output if you use. It should be 'yyyy-MM-dd'.
3. The above code also missed '|' in the concat function. Basically you will have to specify '|' after first two column other wise they will not come.

@Author -
1. Do you have 1900-01-01 in your DB as the value. for this value if you want '' then use 2nd solution else first is the good one to go.

SOLUTION 1 /* Just added CONCAT for you. */

SELECT         dbo.CAPABILITIES.SYCPC_AUTO_KEY, dbo.CAPABILITIES.PNM_AUTO_KEY, dbo.BLOCKED_PN.PN, dbo.BLOCKED_PN.CAP_AUTO_KEY, 
               dbo.BLOCKED_PN.CREATION_DATE, dbo.BLOCKED_PN.NOTES, dbo.BLOCKED_PN.Blocked_date
			  ,CONCAT(FORMAT(b.CREATION_DATE,'yyyy-MM-dd'), '|', b.NOTES , '|' , format(b.Blocked_date,'yyyy-MM-dd')) as Notes
FROM            dbo.CAPABILITIES RIGHT OUTER JOIN
                         dbo.BLOCKED_PN ON dbo.CAPABILITIES.CAP_AUTO_KEY = dbo.BLOCKED_PN.CAP_AUTO_KEY AND dbo.CAPABILITIES.PN = dbo.BLOCKED_PN.PN
WHERE        (dbo.CAPABILITIES.SYCPC_AUTO_KEY IS NOT NULL)
ORDER BY dbo.CAPABILITIES.SYCPC_AUTO_KEY

Open in new window


OR

SOLUTION 2            /* IF you DON't want to show | if the value of the column is NULL/1900-01-01 */

SELECT         dbo.CAPABILITIES.SYCPC_AUTO_KEY, dbo.CAPABILITIES.PNM_AUTO_KEY, dbo.BLOCKED_PN.PN, dbo.BLOCKED_PN.CAP_AUTO_KEY, 
               dbo.BLOCKED_PN.CREATION_DATE, dbo.BLOCKED_PN.NOTES, dbo.BLOCKED_PN.Blocked_date
			  ,CONCAT
			   (
					 CASE WHEN CREATION_DATE IS NOT NULL OR CREATION_DATE = '1900-01-01' THEN '' ELSE FORMAT(b.CREATION_DATE,'yyyy-MM-dd') END, 
					 CASE WHEN NOTES IS NOT NULL THEN CONCAT(' | ' , NOTES) ELSE '' END,
					 CASE WHEN BLOCKED_DATE IS NOT NULL OR BLOCKED_DATE = '1900-01-01' THEN format(b.Blocked_date,'yyyy-MM-dd') ELSE '' END
			   ) AS NOTES
FROM            dbo.CAPABILITIES RIGHT OUTER JOIN
                         dbo.BLOCKED_PN ON dbo.CAPABILITIES.CAP_AUTO_KEY = dbo.BLOCKED_PN.CAP_AUTO_KEY AND dbo.CAPABILITIES.PN = dbo.BLOCKED_PN.PN
WHERE        (dbo.CAPABILITIES.SYCPC_AUTO_KEY IS NOT NULL)
ORDER BY dbo.CAPABILITIES.SYCPC_AUTO_KEY

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
If you want  a separator between elements of the concatenation then use CONCAT_WS()

SELECT
      c.SYCPC_AUTO_KEY
    , c.PNM_AUTO_KEY
    , b.PN
    , b.CAP_AUTO_KEY
    , b.CREATION_DATE
    , b.NOTES
    , b.Blocked_date
    , CONCAT_WS(,'\ '.format(b.CREATION_DATE,'yyyy-MM-dd'), b.NOTES, format(b.Blocked_date,'yyyy-MM-dd')) as cnb
FROM dbo.CAPABILITIES c
INNER JOIN dbo.BLOCKED_PN b ON c.CAP_AUTO_KEY = b.CAP_AUTO_KEY
      AND c.PN = b.PN
WHERE c.SYCPC_AUTO_KEY IS NOT NULL
ORDER BY
      c.SYCPC_AUTO_KEY

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Pawan KumarDatabase ExpertCommented:
@Paul -

Ist - I dont think this function CONCAT_WS() will help as this has been introduced in 2017 and people are hardly using this in their prod environment.

2nd - The syntax you have give for CONCAT_WS() is also INCORRECT, so it will give you compiling error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

3rd - The separator we need in this case is | NOT \

The correct syntax will be below-
CONCAT_WS('|' , FORMAT(d,'yyyy-MM-dd') , FORMAT(d,'yyyy-MM-dd')  )
PortletPaulEE Topic AdvisorCommented:
right, my fat fingers on the phone don't always get it right
Pawan KumarDatabase ExpertCommented:
:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.