GD_GRAY
asked on
How can I alter the result of a SQL Query
If I run a query like this
,,Bingo,,Darts,,Pool,,Word sWithFrien ds,,
how can I code the select statement to change the ,, into a dash - so the result would be
-Bingo-Darts-Pool-WordsWit hFriends-
it would also be nice to omit leading and trailing ,, where the result is
Bingo-Darts-Pool-WordsWith Friends
SELECT games FROM hoby
it will return a value like,,Bingo,,Darts,,Pool,,Word
how can I code the select statement to change the ,, into a dash - so the result would be
-Bingo-Darts-Pool-WordsWit
it would also be nice to omit leading and trailing ,, where the result is
Bingo-Darts-Pool-WordsWith
SELECT REPLACE(SUBSTRING(SUBSTRIN G(games,3, LEN(games) ,1,LEN(gam es)-4),',, ','-')
FROM hoby
FROM hoby
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you can do the substring before the replace -
select replace(substring(games,3, len(games) - 4),',,','-') from hobby;
select replace(substring(games,3,
ASKER
Thank you so very much.
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