How to eliminate the commas from the end in sql query when the column value is null

HI

I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like

select ISNULL(dbo.tblperson.Email1, N'') +';'+ISNULL(dbo.tblperson.Email2, N'') +';'+ISNULL(dbo.tblperson.Email3, N'')ASEmail from tablename.

One eg of the output of the above query when email2,email3 are having null values in the table is :

jacky_foo@.mfa.gov.sg;;

means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.

Please let me know how can i do this

If you just change SQL a bit you have the answer, see below

select ISNULL(dbo.tblperson.Email1+ ';', N'') + ISNULL(dbo.tblperson.Email2 + ';', N'') + ISNULL(dbo.tblperson.Email3 + ';', N'')ASEmail from tablename.

|||

I tried this it worked a bit but not completely.Now I am getting the semicolumn at the end if there is null for the third column or you can say for the last column.

|||

There is probably a quick easy way to do it, but this will work:

select ISNULL(dbo.tblperson.Email1, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email2, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL OR dbo.tblperson.Email2 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email3, N'')ASEmail from tablename.

|||

Another way is to first normalize your data:

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

Now use the normalized data in a query like:

DECLARE @.Email varchar(max)

SELECT @.Email=ISNULL(@.Email+';','')+Email

FROM (

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

) t1

SELECT @.Email AS Email

|||

Another way is to use one of the many string concatenation techniques once your data has been normalized.

There is a CONCATENATE aggregation function that you can install that will do the trick. (Microsoft supplies one somewhere, google "T-SQL string concatenation aggregate").

There is another technique using the FOR XML/PATH to do the same thing, but it's also kind of messy.

|||

I appreciate your reply. This query worked for me.