How can I alter the result of a SQL Query

If I run a query like this
SELECT games FROM hoby

Open in new window

it will return a value like
 ,,Bingo,,Darts,,Pool,,WordsWithFriends,,
how can I code the select statement to change the ,, into a dash - so the result would be
-Bingo-Darts-Pool-WordsWithFriends-  
it would also be nice to omit leading and trailing ,, where the result is
Bingo-Darts-Pool-WordsWithFriends
GD_GRAYAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
To eliminate beginning and ending ',,' -
select substring(replace(games,',,','-'),2,len(replace(games,',,','-')) - 2)
from hobby;
0
 
thenerdynerdCommented:
Is this just one column with all that data?  

Best solution would be to split the games data into separate columns that would be best practice, then in a statement you can manipulate much easier.  I suspect your data looks like the last line in the before SQL screen shot?

In any case here is the SQL that I think you are after:

SELECT REPLACE(games,',,','-') games FROM Hobbies
WHERE games IS NOT NULL
Screen-Shot-2013-06-19-at-18.36..jpg
Screen-Shot-2013-06-19-at-18.45..jpg
0
 
selva_konguCommented:
SELECT REPLACE(SUBSTRING(SUBSTRING(games,3,LEN(games),1,LEN(games)-4),',,','-')

FROM hoby
0
 
awking00Commented:
Or you can do the substring before the replace -
select replace(substring(games,3,len(games) - 4),',,','-') from hobby;
0
 
GD_GRAYAuthor Commented:
Thank you so very much.
0
All Courses

From novice to tech pro — start learning today.