inserting "&" into results of concatenation query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inserting "&" into results of concatenation query

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-10-21 : 02:17:14

With help from the members of this forum (thanks, again and again) I have a query that successfully concatenates the names of all animals belonging to one customer into a string, which I then use in an application for a variety of tasks.

UPDATE dbo.customerDetails
SET petNames = stuff(( select ', '+ petName from petDetails
WHERE customerID = c.customerID for xml path('')),1,1,'')
FROM customerDetails c

The output currently looks like this for a customer with 3 animals - "Woofy, Smelly, Birdy"

One of the tasks the variable is used for is generating an email - and I would like to present these names 'nicely' or more grammatically correct, like this: "Woofy, Smelly & Birdy" (note the inserted ampasand).

If I was concatenating only two names it would, of course, be easy. But as one customer can (and often does) have 'n' number of pets, I don't know how to do it - or indeed if it is possible at all.

Here's some test data if it will help.

table.customerDetails
custID customerName petNames
1 Smith
2 Jones
3 Bertram
4 Betty

table.petDetails
petID custID petName
1 1 Woofy
2 1 Smelly
3 2 Huffle
4 3 Blue
5 3 Shrek
6 4 Barker

And of course, if anyone can suggest a better, cleaner, more efficent, more professional or just simpler method of achieving the objective - then triple virtual Malt Whisky all round!

Many thanks

Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 11:52:28

just do this update after your current one


UPDATE dbo.customerDetails
SET petNames =stuff(petNames,LEN(petNames)-CHARINDEX(',',REVERSE(petNames))+1,1,'&')
where CHARINDEX(',',petNames) > 0

nick_dkc
Starting Member

25 Posts

Posted - 2009-10-22 : 01:24:12

Many thanks Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 12:47:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources