1

I am working on linguistic analyses on Programmers. I am running a query in the Data Explorer that grabs all the user information. The query is the following:

select * from Users;

The query is fine but some fields include commas, which messes up the CSV when I try to import it into Excel. How would you modify the query so that commas could be replaced by something else such as a space?

8
  • 1
    @CloseVoter : Where would a question about the Data Explorer query go if not here ? Jun 3, 2013 at 20:51
  • 1
    Stack Overflow @return, this is a SQL question and I'm the obvious close voter as I say it in my answer :-). Jun 3, 2013 at 20:52
  • @benisuǝqbackwards didn't read that far haha thanks :P Jun 3, 2013 at 20:53
  • The problem is likely stray quotation marks, by the way, as the values are qualified by quotes in the generated CSV.
    – Tim Stone
    Jun 3, 2013 at 21:04
  • sigh, breaking my answer @Tim... can't you fix that in the export? (once some code gets pushed there :-) Jun 3, 2013 at 21:07
  • I think that Excel accepts "" as an escape for an embedded double-quote, so it might be sensible if Data Explorer handled that itself (or allowed for it to be configurable).
    – Tim Stone
    Jun 3, 2013 at 21:09
  • It does @Tim, yes. <generic whine about Microsoft and standards/> Jun 3, 2013 at 21:11
  • @benisuǝqbackwards Huh, Data Explorer actually does do this already, so I'm not sure what happened in the OP's case.
    – Tim Stone
    Jul 14, 2013 at 13:35

1 Answer 1

2

Use REPLACE()

select id, replace(<comma field>, ',', ' ') as some_column
  from users

P.S. This is really off-topic for MSO.

If, as Tim says, it's stray quotation marks then you need to replace them with 2 as per RFC 4180

select id, replace(<comma field>, '"','""') as some_column
  from users
1
  • Put two replaces in there @user1029296 replace(replace(<blah>,'a', 'b'), 'g', 'h') The point isn't to replace the quotation marks though, it's to make your extract to conform to the RFC so that they remain in your data. Jun 3, 2013 at 21:10

Not the answer you're looking for? Browse other questions tagged .