How to place a delimiter using CONCAT

I need to place a pipe or vertical bar as shown below using the select statement also shown below:

1/1/2009|BLOCK AS PER CANCELLATION MEETING|6/21/2017

SELECT [SQL]
      ,[PNM_AUTO_KEY]
      ,CONCAT(ISNULL(CREATION_DATE,','),ISNULL(NOTES,''),ISNULL(BLOCKED_DATE, '')) AS NOTES
      ,[Blocked]
      ,[CAP_AUTO_KEY]
  FROM [QUANTUM_LIVE].[dbo].[MIN_MAX]

Open in new window

maximus1974Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Replace the notes field as shown below

CONCAT(ISNULL( CONVERT(VARCHAR(10),CREATION_DATE,101)+'|',','),ISNULL(NOTES+'|',''),ISNULL(CONVERT(VARCHAR(10),BLOCKED_DATE,101), '')) AS nOTES
0
 
Bill PrewCommented:
Please try:

SELECT [SQL]
      ,[PNM_AUTO_KEY]
      ,CONCAT(ISNULL(CONVERT(VARCHAR(10),CREATION_DATE,101),''),'|',ISNULL(NOTES,''),'|',ISNULL(CONVERT(VARCHAR(10),BLOCKED_DATE,101),'')) AS NOTES
      ,[Blocked]
      ,[CAP_AUTO_KEY]
  FROM [QUANTUM_LIVE].[dbo].[MIN_MAX]

Open in new window


»bp
0
 
Scott PletcherSenior DBACommented:
One of the big advantages of using CONCAT is that you don't need ISNULL just to skip an entry.  You would need it to substitute a ',' for a NULL, though.  I assume you want both |s to appear even if a NULL value is present.  If you don't, let me know, and we can change the query.

SELECT
...
    CONCAT( ISNULL(CREATION_DATE,','), '|', NOTES, '|', BLOCKED_DATE ) AS NOTES
...
0
 
Pawan KumarDatabase ExpertCommented:
I think there is not point showing | if the columns values is NULL.
We can use this approach..if the value is there then add '|' + columnValue.

Eg.if notes is NULL then we will get ..

this 1/1/2009|6/21/2017

Instead of 1/1/2009||6/21/2017

Please use..

--

SELECT [SQL]
      ,[PNM_AUTO_KEY]
      ,CONCAT
	   (
			 ISNULL(CREATION_DATE,',') , 
			 CASE WHEN NOTES IS NOT NULL THEN CONCAT(' | ' , NOTES) ELSE '' END,
			 CASE WHEN BLOCKED_DATE IS NOT NULL THEN CONCAT(' | ' , BLOCKED_DATE) ELSE '' END
	   ) AS NOTES
      ,[Blocked]
      ,[CAP_AUTO_KEY]
FROM [QUANTUM_LIVE].[dbo].[MIN_MAX]

--

Open in new window

0
 
Scott PletcherSenior DBACommented:
If you don't want to show the | if the value is null -- unsusual, but possible -- there's still not need for a CASE statement:

CONCAT( ISNULL(CREATION_DATE,','), '|' + NOTES, '|' + BLOCKED_DATE ) AS NOTES
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.