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  ....
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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.BAuthor Commented:
I can't,
this is a client entry.

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

thanks
Jim HornSQL Server Data DudeCommented:
I'm afraid the above is correct:  If you have a comma-separated text file, then the values in it cannot have commas in it, as it will cause the ETL process that consumes that file to interpret the row as having more columns than is currently mapped, and fail.

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...
SELECT
   REPLACE(CONVERT(varchar,InvoiceNumber), ',', '') AS [InvoiceNumber],
   REPLACE(CONVERT(Varchar,Reference), ',', '')  as [Reference]
FROM FinalizedOrders
WHERE  ....

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

W.E.BAuthor Commented:
I've requested that this question be deleted for the following reason:

Found own sloution
Jim HornSQL Server Data DudeCommented:
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.BAuthor Commented:
SELECT
          CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
'"' + Cast(Reference as varchar(100)) + '"' AS [Reference]
       FROM FinalizedOrders
       WHERE  ....
Neil RussellTechnical Development LeadCommented:
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.BAuthor Commented:
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.BAuthor Commented:
I will award the points to Jim, for his effort and time.

Thank you Jim.
Jim HornSQL Server Data DudeCommented:
We must have cross-posted the last comment, and my objection to the close request.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.