how to handle single quote in PATINDEX

Posted on 2009-02-24
Last Modified: 2012-05-06
I'm trying to parse the names that come in like Doe, John to John Doe and we now have a name like Cox, J'son (which I think is giving me a problem). Is there something I can do to allow quotes in the names?
thanks for your help
RIGHT(dbo.udfTasksPrimaryClosed.ClosedByNameFull,LEN(dbo.udfTasksPrimaryClosed.ClosedByNameFull)-PATINDEX('%,%',dbo.udfTasksPrimaryClosed.ClosedByNameFull)-1) + ' ' +

   SUBSTRING(dbo.udfTasksPrimaryClosed.ClosedByNameFull,1,PATINDEX('%,%',dbo.udfTasksPrimaryClosed.ClosedByNameFull)-1) AS ClosedBy,

   Count(dbo.udfTasksPrimaryClosed.TaskNumber) as ClosedCount,

   AVG(dbo.fn_WorkDays([Created], [Closed])) as ClosedDays

Open in new window

Question by:avoorheis
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please confirm that you are using MS SQL Server and not MySQL ...

    now, the code you posted in itself is not a problem in regards to the quotes, so it must be some other code.
    please post the relevant code.

    Author Comment

    sorry, there was another issue.

    However, it is MS SQL and I'm converting a name field from Last, First to First Last (so just the first 2 lines of code, mistakenly copied an additional 2 lines). Seems like a lot to do this, but, I'm a there a better way?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I still don't see the issue...

    Author Comment

    I should have been more's working ok the way it is and does make the swap last/first (there was another issue).
    I thought since I had already posted, you might comment on whether the sql that I've used is the best approach for doing what I want.
    LVL 142

    Accepted Solution

    I don't see any issue, specifically if you tested that it works.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now