Post reply

Remove the double quote

  • February 23, 2015 at 11:35 am

    #315668

    Hi,

    i have a SQL select syntax as below

    0 AS SalaryMin,

    2088 AS SalaryMax,

    2088 AS BillableHours,

    'Month' AS SalaryPaidCode,

    0 AS SalaryBreakdownHourly,

    0 AS SalaryBreakdownDaily,

    0 AS SalaryBreakdownWeekly,

    0 AS SalaryBreakdownBiweekly,

    0 AS SalaryBreakdownSemiMonthly,

    0 AS SalaryBreakdownMonthly,

    0 AS SalaryBreakdownAnnually,

    'N/A' AS BargainingUnitCode,

    'N/A' AS PhysicalClassCode,

    'G' AS BenefitCode,

    'N/A' AS OccupationalGroupCode,

    'Exempt' AS FLSA,

    'Other' AS EEO,

    1 AS AcceptJobInterestCards,

    NULL AS Notes

    while outputting to CSV.file

    i got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1

    how can i remove all double quotes in the string fields? so that i can get the result as below while the output

    0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1

    thank you.

  • February 23, 2015 at 11:48 am

    #1779364

    whatever you are exporting with has got the text qualifier turned on; just turn it off./ set it to a blank string.

    if you were exporting from SSMS via the export wizard, it looks like this, for example:(number six in the image)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 23, 2015 at 11:54 am

    #1779369

    Thank you Lowell.

    Is there anyway i can eliminate the double quotes from my script?

    thank you.

  • February 23, 2015 at 11:58 am

    #1779371

    kennyhuang0108 (2/23/2015)

    Thank you Lowell.

    Is there anyway i can eliminate the double quotes from my script?

    thank you.

    there are no double quotes generated in the SELECT statement you posted. there are single quotes around some strings, but those are required to make the sql command work.

    whatever CONSUMES that sql statement is adding double quotes around char/varchar data types...so that has nothing to do with the query itself.

    what is actually writing to the CSV file?

    are you using SSIS? export wizard? a CLR? bcp? sqlcmd?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 23, 2015 at 3:19 pm

    #1779431

    Thanks Lowell.

    I just checked with my boss and the strings can be double quoted.

    Thank you very much for your time.

    Hope in the future, i wont post a stupid question.

    Sincerely,

    Kenny

  • February 23, 2015 at 10:28 pm

    #1779479

    kennyhuang0108 (2/23/2015)

    Thanks Lowell.

    I just checked with my boss and the strings can be double quoted.

    Thank you very much for your time.

    Hope in the future, i wont post a stupid question.

    Sincerely,

    Kenny

    It wasn't a stupid question. My question would be, what are you using to do the output to the CSV file with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 24, 2015 at 2:32 pm

    #1779754

    Hi Jeff,

    I need to output to text file so that another department can upload to its DB and migrate to another system.

    That is the whole project about.

    Thanks.

  • February 25, 2015 at 4:32 pm

    #1780087

    kennyhuang0108 (2/24/2015)

    Hi Jeff,

    I need to output to text file so that another department can upload to its DB and migrate to another system.

    That is the whole project about.

    Thanks.

    If they have the same version of SQL Server as you, a native export using BCP would be very efficient and there would be no worries about delimiters, double quotes, or anything else.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply