remove end character that produce by sql query

Guys,
How do I in sql server to remove all the  end  ",,,,,,,,,"  character below ?? I have a sql query that  combine the columns into one columns

  --,[l]
  ,
  [mail1] + ',' +
  [mail2] + ',' +
  [mail3] + ',' +
  [mail4] + ',' +
  [mail5] + ',' +
  [mail6] + ',' +
  [mail7] + ',' +
  [mail8] + ',' +
  [mail9] + ',' +
  [mail10] + ',' AS [Worker_Email_Adrress],
  [manager_name]
  [managerEmployeeNumber]

Open in new window


and the result whenever any of the mail column empty . it left the comma behind  as below, how do I make sure my query does not return the commas below if the column is empty.

Peter.Gadder@yahoo.com,Peter.Gadder@rhx.com,,,,,,,,,
Peter.Gadder@yahoo.com,Peter.Gadder@rhx.com,,,,,,,,,
nasiruddin.Hafifi@yahoo.com,nasiruddin.Hafifi@rhx.com,,,,,,,,,
nasiruddin.Hafifi@yahoo.com,nasiruddin.Hafifi@rhx.com,,,,,,,,,
Paravat-umapathy@yahoo.com,Paravat-umapathy@rhx.com,,,,,,,,,
Paravat-umapathy@yahoo.com,Paravat-umapathy@rhx.com,,,,,,,,,
motioneyeAsked:
Who is Participating?
 
Arifhusen AnsariConnect With a Mentor Business Intelligence Developer and AnalystCommented:
I agree with Olaf Doschke. To do get the result as per your request. You need to covert blank to null or you can make your field nullable so if there is no data it will have null into it.

But second option will cause you to do changes in some other logic. so you can use below logic for the same with example table.
SO rather than appending "," at the end start appending "," from second column from start.

And for your clarification NULLIF will match first argument data with second one and if it matches, it will convert into null. Hence we have put '' ( Blank) as second argument. HOpe it will help you.


CREATE TABLE #Tmp
(

	ID INT
	,MAIL1 VARCHAR(50)
	,MAIL2 VARCHAR(50)
	,MAIL3 VARCHAR(50)
	,MAIL4 VARCHAR(50)
)

INSERT INTO #Tmp
VALUES 
(1,'xya@test.com','sdfa@tea.com','','asdfas@fasd.com')
,(2,'xya@.com','','sdfa@tea.com','')
,(3,'xya@test.com','sdfa@tea.com','asf@dfa.com','dfad@dfad.com')

,(4,'xya@test.com','','','')

SELECT ID, MAIL1+','+MAIL2+','+MAIL3+','+MAIL4 FROM #Tmp

-- New query

SELECT ID
		,
		CONCAT(
		COALESCE(NULLIF(MAIL1,''),'') 
		,COALESCE(','+NULLIF(MAIL2,''),'')
		,COALESCE(','+NULLIF(MAIL3,''),'')
		,COALESCE(','+NULLIF(MAIL4,''),'')
		)
FROM #Tmp
--DROP TABLE #Tmp

Open in new window

0
 
Geert GConnect With a Mentor Oracle dbaCommented:
i'd substring from the first occurence of ,,

case when patindex(',,', yourcolumn) > 0 then substring(yourcolumn, 1, patindex(',,', yourcolumn)-1) else yourcolumn end

Open in new window

0
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
coalesce([mailN]+',','') + ...

Open in new window


That either puts in a mail plus comma or nothing. Requires "empty" mails to be NULL, though, which they aren't if you get the results you get, so perhaps change that for COALESCE to work out. Make mail fields nullable and turn empty fields to NULL. The nice thing about NULL is NULL+',' stays NULL, so it's removed, unless mail isn't NULL and you get mail+','

Besides scrapping all the fields and having a 1:n related mailadress table to only have as many records as mail addresses and all of them only when filled. Makes creation of CSV easy via XMLPath (https://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/)

Any table design with numbered columns has to be redesigned, it just leads to such problems you have right now. Normalization of data is not just a theory making things harder for part time DB developers, hobbyist or enthusiast, it's not just what pros do, it's really practical.

Bye, Olaf.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
If the mailN is an empty string instead of NULL, and I suppose so (otherwise you wouldn't get a result at all), you can use a case for each field:
+ case when mailN != '' then mailN + ',' else '' end +

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
Correct, Qlemo, but makes it an unfortunatly long expression. Therefore I talked about how to make it nullable fields or even convert the table to two.

In the end xml path also isn't very intuitive, if you go for data normalization, but it is the most concise creation of a comma separated list from the correct outset of data, that T-SQL offers.

Bye, Olaf.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
if any of the mail columns is NULL, the the whole set of columns being + would also be null, so they must be a zero length string.

e.g   [mail9] + ',' + [mail10]  if either is null then the whole lot will be null

so  if   [mail9] is NULL    then     [mail9] + ','    will still be NULL. Therefore [mail9] must be '' (ie a zero length string)

So, no point checking for NULL in this specific case (although should do it anyway for the above mentioned reasons)
but can cover both conditions.... Assuming SQL2012 (please advise otherwise)
declare @mail1 varchar(10) = 'abc@123'
declare @mail2 varchar(10) = ''
declare @mail3 varchar(10) = ''
select iif(len(@mail1 + ',')>1,@mail1 +',','') +
       iif(len(@mail2 + ',')>1,@mail2 +',','') +
       iif(len(@mail3 + ',')>1,@mail3 +',','') 

Open in new window

If pre-sql2012 then have to use CASE e.g.
declare @mail1 varchar(10) = 'abc@123'
declare @mail2 varchar(10) = ''
declare @mail3 varchar(10) = ''
select case when len(@mail1 + ',') > 1 then @mail1 +','else '' end +
       case when len(@mail2 + ',') > 1 then @mail2 +','else '' end +
       case when len(@mail3 + ',') > 1 then @mail3 +','else '' end 

Open in new window

Now it is still possible to end up with a trailing comma, so that has to be fixed.... easiest is to have an escape sequence of some sort that wouldnt be found in an email address. I will use $$$.

So, applying that logic in your case
 --,[l]
  ,replace(
  iif(len([mail1] + ',')>1,[mail1] +',','') +
  iif(len([mail2] + ',')>1,[mail2] +',','') +
  iif(len([mail3] + ',')>1,[mail3] +',','') +
  iif(len([mail4] + ',')>1,[mail4] +',','') +
  iif(len([mail5] + ',')>1,[mail5] +',','') +
  iif(len([mail6] + ',')>1,[mail6] +',','') +
  iif(len([mail7] + ',')>1,[mail7] +',','') +
  iif(len([mail8] + ',')>1,[mail8] +',','') +
  iif(len([mail9] + ',')>1,[mail9] +',','') +
  iif(len([mail10] + ',')>1,[mail10] +',','') + '$$$' , ',$$$','') AS [Worker_Email_Adrress],
  [manager_name],
  [managerEmployeeNumber]

Open in new window

Or do it  so comma is before the [mail?] column  ie    ','+[mail1]   then use
stuff(<list of mail columns strung together>,1,1,'')
0
 
awking00Connect With a Mentor Commented:
Assuming there will not be any spaces in an email address, replace all commas with spaces, rtrim the spaces, then replace the remaining spaces with a comma. -

with cte as
(<your query>)
select
 --,[-1]
,replace(rtrim(replace([Worker_Email_Address],',',' ')),' ',',') as [Worker_Email_Addresses],
[manager_name],
[managerEmployeeNumber],
...
from cte;
0
 
motioneyeAuthor Commented:
Many thanks all,
I have tried all of the suggestions and  have  to say thanks as  all worked  with the recommendations. I really appreciates this help :)
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.