PLS HELP ME ..STRING CONCATINATION

rajukurian · May 6th, 2005, 10:53 AM

Hi
        I am facing a problem in string concatination in sql server...i am taking 5 values from 5 diff columns...and i have to check if its not null then concatinate with "|"...

        select pgcd1 + "|"+ pgcd2 +"|" +pgcd3 + "|"+ pgcd4 as PGCD FROM TABLENAME...

         The problem i am facing is ...the "|" symbol should comes only in pictures if the field is having any value...

       desired output 1)- if ALL 3 fields r having values then

            pgcd1 | pgcd2 | pgcd3

2) if first and last having values and second is null then

            pgcd1 | pgcd3

3) IF FIRST IS NULL AND SECOND AND THIRD COLUMNS R HAVING VALUE THEN

            PGCD2 | PGCD3

   Can anybody help me....thx

jbenson001 · May 6th, 2005, 12:43 PM

Can you give more explination on why you want to set up your query this way?

rajukurian · May 6th, 2005, 12:49 PM

actually ..thats the user requiremnt...They want the combination of the 5 columns in one column. ..all the five columns r coming from diff tables....and i have to join it using "|" symbol in one field ...

gbianchi · May 6th, 2005, 12:55 PM

hi there..

you should depure this.. but thinking something like

SELECT
iif(isnull(pgcd1),"",pgcd1+"|")+... etc

i dont remember exact function for sql server.. but after that you will have to take always the last "|" ..

HTH

Gonzalo

rajukurian · May 6th, 2005, 01:15 PM

select if(isnull (pgmcd1),"",pgmcd1,"|") as pgmcd from Temp_DISPromo_GRP1

not working...i guess something wrong in syntax

shahchi1 · May 6th, 2005, 01:20 PM

SELECT CASE
       WHEN (column1 IS NOT NULL) THEN column1 + space(1) + '|' + space(1) ELSE '' END +
       CASE
       WHEN (Column2 IS NOT NULL) THEN column2 + space(1) + '|' + space(1) ELSE '' END +
       CASE
       WHEN (Column3 IS NOT NULL) THEN column3 + space(1) + '|'+ space(1) ELSE '' END
From MyTable

gbianchi · May 6th, 2005, 01:42 PM

mmm Chirag you still need the substr to get out the last 2 chars b/c if column3 is null you will end with something like col1 + col2 + " | " ..

im ok????

HTH

Gonzalo

rajukurian · May 6th, 2005, 01:50 PM

ya...still i am struggling to get the desired results..

rajukurian · May 6th, 2005, 01:56 PM

SELECT

       CASE
          WHEN (pgmcd1 IS NOT NULL) THEN pgmcd1 + space(1) + '|' + space(1) ELSE '' END +
       CASE
          WHEN (pgmcd2 IS NOT NULL) THEN pgmcd2 + space(1) + '|' ELSE '' END +
       CASE
          WHEN (pgmcd3 IS NOT NULL) THEN pgmcd3 + space(1) + '|' ELSE '' END +
        CASE
          WHEN (pgmcd4 IS NOT NULL) THEN pgmcd4 + space(1) + '|' ELSE '' END +
        CASE
          WHEN (pgmcd5 IS NOT NULL) THEN pgmcd5 + space(1) + '|' ELSE ''
       END
as pgmcd from Temp_DISPromo_GRP1

STILL I AM GETING THE RESULT LIKE..

================================================== =======

  | | | | |

A | |C | | E |

  |B | C | | E |

SOME OTHER SYNTAX I GUESS

rgerald · May 6th, 2005, 02:11 PM

Try this sample:

Code:

DECLARE @s1 varchar(10)
DECLARE @s2 varchar(10)
DECLARE @s3 varchar(10)
DECLARE @s4 varchar(30)

SET @s1 = NULL
SET @s2 = 'two'
SET @s3 = 'three'

SET @s4 = '|' + isnull(@s1 + '|', '') + isnull(@s2 + '|', '') + isnull(@s3 + '|', '')
Print @s4

Rand