[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

remove comma at end of query output

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
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
PeterBaileyUkAuthor Commented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
PeterBaileyUkAuthor Commented:
Apologies for that Vitor. I made reference to the id's in the posts of course.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now