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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle 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 DoschkeSoftware 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
Qlemo"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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Arifhusen AnsariBusiness 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic 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
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.