SQL data has commas in it

Posted on 2009-02-20
Last Modified: 2012-05-06
I have columns with imbedded commas (like company name, etc.) and it kills my sql select when compounding multiple columns together;

SELECT  lastname + ' ' + firstname + '  ' + Companyname as LongName

The result is truncated where there is a comma in the company name.  It's not uncommon to use commas in names and addresses.  Surely there is a way to return the entire value.
Question by:larksys
    LVL 25

    Expert Comment

    What are your field lengths? Try -

    SELECT  CAST(lastname + ' ' + firstname + '  ', varchar(500)) + CAST(Companyname as varchar(500) as LongName
    LVL 68

    Accepted Solution

    SQL itself doesn't have a problem with commas in the data.  The problem is very likely on the receiving/display end.
    LVL 40

    Expert Comment

    Your query will not trunacte the characters of your firstname. lastname or companyname.
    SELECT  lastname + ' ' + firstname + '  ' + Companyname as LongName
    As ScottPletcher, there may be problem by recieving application. check that one. if you think, you have issues while executing in SSMS, post the issue.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    If the problem is that you are exporting to a CSV file, than there are ways to workaround that.  But this is unrelated to SQL Server.
    LVL 1

    Author Comment

    It appears that you guys are correct.  Thanks.  It has to do with the coldfusion valuelist function.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now