Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I alter the result of a SQL Query

Posted on 2013-06-19
5
Medium Priority
?
258 Views
Last Modified: 2013-06-19
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
0
Comment
Question by:GD_GRAY
5 Comments
 
LVL 1

Expert Comment

by:thenerdynerd
ID: 39260367
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
 
LVL 9

Expert Comment

by:selva_kongu
ID: 39260429
SELECT REPLACE(SUBSTRING(SUBSTRING(games,3,LEN(games),1,LEN(games)-4),',,','-')

FROM hoby
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 39260450
To eliminate beginning and ending ',,' -
select substring(replace(games,',,','-'),2,len(replace(games,',,','-')) - 2)
from hobby;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39260461
Or you can do the substring before the replace -
select replace(substring(games,3,len(games) - 4),',,','-') from hobby;
0
 

Author Closing Comment

by:GD_GRAY
ID: 39260950
Thank you so very much.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

782 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