asked on

SQL comma

Hello,
I use below script to export data, (and save to csv through an exporter program,
the Reference field has commas in it,
when I export , the fields with commas become separated, , I need to keep the field together.
Example Reference : 100sd675,PMExvtyr

      SELECT
       CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
            CONVERT(Varchar,Reference) as [Reference]
      FROM FinalizedOrders
      WHERE ....

Aneesh

easiest option is to make a different character instead of a comma for separating the fields say !

SELECT
CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
'!'+ CONVERT(Varchar,Reference) as [Reference]
FROM FinalizedOrders
WHERE ....

W.E.B

ASKER

I can't,
this is a client entry.

I found my own solution though,
'"' + Cast(Reference as varchar(100)) + '"' AS [Reference] ,

thanks

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

I've requested that this question be deleted for the following reason:

Found own sloution

Jim Horn

Objection. Spell out what 'Found own solutions' means here, so we can accept that as the answer, so other people with similar issues can learn from this question.

W.E.B

ASKER

SELECT
CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
'"' + Cast(Reference as varchar(100)) + '"' AS [Reference]
FROM FinalizedOrders
WHERE ....

Neil Russell

Objection? Jim he spelled out what his solution was already! Enclose the string in " ". Normal for any CSV that has commas in it.

W.E.B

ASKER

So , my own solution (Comment before Jim Comment)
'"' + Cast(Reference as varchar(100)) + '"' AS [Reference]

Is the same as Jim?

Jim says,
Your choices:
Change the delimeter to something else, such as pipe (|) delimited file, as pipes are hardly every used in values, except for columns that contain email text where some people's signature blocks have pipes in them, such as Dave Swift | Super Dude | Green Co.

Use the REPLACE function to either remove the commas, or replace them with some other value...

?

W.E.B

ASKER

I will award the points to Jim, for his effort and time.

Thank you Jim.

Jim Horn

We must have cross-posted the last comment, and my objection to the close request.