SQL 2005 - Parse CSV field

Posted on 2007-08-05
Last Modified: 2008-09-05
I have a field (f2) in a tblImportError that has names, with no spaces, seperated by commas (last,first,middle)

This code works perfectly, until we come to a name that has a first name with one character and no middle name.  It will error out.
Changing the SUBSTRING(REVERSE(f2), 2, 1) to SUBSTRING(REVERSE(f2), 1, 1) in the key2 line leaves commas after the last name.

update tblImportError
    Set key2 = SUBSTRING(f2, CHARINDEX(',', f2) + 1, LEN(f2) - (CHARINDEX(',', f2) + 0) - CASE WHEN  
         SUBSTRING(REVERSE(f2), 2, 1) = ',' THEN 1 ELSE 0 END) -- firstname
       ,key3 = LEFT(f2, CHARINDEX(',', f2) - 1) -- lastname
       ON tblImportError.key1 = tblImport1.f1

Any help is appreciated!!!
Question by:fhcdaver
    LVL 42

    Expert Comment

    update tblImportError
     set key2=substring(f2+ ',,',1,charindex(',',f2+',,')-1) --lastname
    ,set key3=substring(substring(name+ ',,',charindex(',',name+',,')+1,len(name+',,')-charindex(',',name+',,')+1)
    ,1,charindex(',',substring(name+ ',,',charindex(',',name+',,')+1,len(name+',,')-charindex(',',name+',,')+1))-1
    ) --firstname
    LVL 6

    Accepted Solution

    Here's a great application for a user-defined function.  This is one that I wrote long ago, and have used many times...

    create function dbo.fn_Subfield(@string varchar(8000), @sep char(1), @count int)
        returns varchar(8000)
        -- Parses a delimited field, returning the desired subfield.
        -- Parameters:
        -- @string - the delimited field
        -- @sep - single-character separator (comma, period, etc.)
        -- @count - which subfield to return (1 = 1st, 2=2nd, etc.)
        declare @result varchar(8000)
        declare @ptr int

        while @count > 0 begin
            set @ptr = charindex(@sep, @string)
            if @ptr = 0 set @ptr = len(@string) + 1
            if @count = 1 break;
            set @string = substring(@string, @ptr+1, 8000)
            set @count = @count - 1
        set @result = substring(@string, 1, @ptr-1)    
        return @result

    Using this function your problem would be solved as follows:

    update tblImportError
        Set key2 = dbo.fn_Subfield(f2, ',', 2) -- firstname
           ,key3 = dbo.fn_Subfield(f2, ',', 1) -- lastname
           ON tblImportError.key1 = tblImport1.f1

    Much easier to read, and you can just as easily use it to extract the 7th subfield from a 10-field delimited list, if you like.  

    Hope this helps.

    Author Comment

    This was a fast and easy solution.
    Thank you!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    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…

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now