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.
