[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

remove comma at end of query output

Posted on 2016-09-16
13
Medium Priority
?
114 Views
Last Modified: 2016-09-16
I have this query and its output is great but would like to not have the end comma
output
select TW.ClientCode,tw.word,
			   
                ((
						select 
							cast(ascii(substring(tw.word,N,1)) as varchar(5))+',' 
						from 
							[dictionary].[dbo].[fnTally]() nr
						where
							nr.N<=len(tw.word) 
						FOR XML PATH('')
					) ) 
                AS cusr
from Tblwords TW
where clientcode='54553002'
group by TW.ClientCode,tw.word

Open in new window

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41801148
Peter, looks like you changed something from the solution I provided you yesterday. I had the comma at the beginning and the STUFF function were intended to remove the first comma.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41801154
You can still use the STUFF function with your adapted query but you need to provide the correct parameters:
STUFF ( character_expression , start , length , replaceWith_expression )  
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41801157
Vitor, put it back, this is not against current rules, it's not a link to a competitive site but to a blog article, and it explains STUFF in all completeness of how to use it. I couldn't write it better, so why not simply point there?

Bye, Olaf.
1
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:PeterBaileyUk
ID: 41801159
its a different sequence, I moved forward in the design yours gave perfectly
the ascii totals for each word and gave the sequence.
I moved onto character sequencing so that I could detect
'SE' VS 'ES' and the ascii summing doesnt work there but character sequencing does.

so this solution does that, I tried combining yours but couldnt do it and I think we are a third of a day apart so missed each other.
0
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41801160
Well surely is not elegant but it should do the job
SELECT ClientCode
	,Word
	,SUBSTRING(cusr, 1, LEN(cusr) - 1)
FROM (
	SELECT TW.ClientCode
		,tw.word
		,(
			(
				SELECT cast(ascii(substring(tw.word, N, 1)) AS VARCHAR(5)) + ','
				FROM [dictionary].[dbo].[fnTally]() nr
				WHERE nr.N <= len(tw.word)
				FOR XML PATH('')
				)
			) AS cusr
	FROM Tblwords TW
	WHERE clientcode = '54553002'
	GROUP BY TW.ClientCode
		,tw.word
	) Source

Open in new window

0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41801162
Please try this , I have used stuff

--

select TW.ClientCode,tw.word,
			   Stuff
                ((
						select 
							cast(ascii(substring(tw.word,N,1)) as varchar(5))+',' 
						from 
							[dictionary].[dbo].[fnTally]() nr
						where
							nr.N<=len(tw.word) 
						FOR XML PATH('')
					),1,1,'' ) 
                AS cusr
from Tblwords TW
where clientcode='54553002'
group by TW.ClientCode,tw.word


--

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41801163
Vitor, put it back, this is not against current rules, it's not a link to a competitive site but to a blog article
Olaf it was hard for me to delete your comment because I know you're not a kind of a google answer guy but the rule states about Blind Links and not a link to a competitive site (that's another rule). We as Experts when providing links to articles we need to add something more than just the link itself.
Cheers
0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41801164
If the above does not work then use below

--

select TW.ClientCode,tw.word,
			   Stuff
                ((
						select 
							', ' + cast(ascii(substring(tw.word,N,1)) as varchar(5)) 
						from 
							[dictionary].[dbo].[fnTally]() nr
						where
							nr.N<=len(tw.word) 
						FOR XML PATH('')
					),1,2,'' ) 
                AS cusr
from Tblwords TW
where clientcode='54553002'
group by TW.ClientCode,tw.word

--

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41801165
Yours is still here Vitor this helps me determine if a shuffling of words took place between months. the character by character sequence to cover the other states. word by word ascii sum sequence
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41801169
Understood, Vitor, but you are not forced to live by the rules literally, if you see the article speaks for itself. It's stupid to repeat anything the article said just to accompany it with own words. It's a useful link and thus not against the notion and meaning of the rule, it's also not a lengthy page where it's hard to pick out the needed answer.

You're misusing your power here in my oppinion, even though you just live by the rules. That's rude.

Bye, Olaf.
1
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41801173
Peter you could used the same solution for this problem also. In fact it was Pawan provided you even he left an space after the comma and that's why you have the 2 in STUFF's length parameter.
0
 

Author Comment

by:PeterBaileyUk
ID: 41801177
Apologies for that Vitor. I made reference to the id's in the posts of course.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question