Parsing strings from a field value

  • Hi,

    I created an ETL process using C# (SSIS was not available) and ran into some issues with one of the fields.

    I have a [description] that contains data similar to this "Total for customer 000999 ACME INDUSTRIAL ". I want to extract the customer number (in this case) 00999 (notice I dropped one zero) load (update this data to a customer number field within the same table). Extract the ACME INDUSTRIAL to the customer name field within the same table. I've tried using Substring with CHARINDEX and other string functions. How can I extract this data? It seems straightforward but I'm getting stuck. Any help would be appreciated.

    Regards,

    Note this data for this description field is consistent and the source can't be changed on the customer's side.

  • Joe Contreras-290946 (7/9/2014)


    Hi,

    I created an ETL process using C# (SSIS was not available) and ran into some issues with one of the fields.

    I have a [description] that contains data similar to this "Total for customer 000999 ACME INDUSTRIAL ". I want to extract the customer number (in this case) 00999 (notice I dropped one zero) load (update this data to a customer number field within the same table). Extract the ACME INDUSTRIAL to the customer name field within the same table. I've tried using Substring with CHARINDEX and other string functions. How can I extract this data? It seems straightforward but I'm getting stuck. Any help would be appreciated.

    Regards,

    Note this data for this description field is consistent and the source can't be changed on the customer's side.

    Maybe I misunderstand you but I don't remember C# having a String Function called CHARINDEX.

    How exactly are you manipulating the string and attempting to perform the UPDATE?

  • Steps:

    1. Taking the XLS data and using the bulkcopying object to a staging table (using C# instead of SSIS)

    2. From within SQL Server 2008 I'm trying to parse the string to the various fields I want. This is where I tried using CHARINDEX combining with Substring and LEFT/RIGHT but I'm just creating a mess :crazy:

    Regards

  • Joe Contreras-290946 (7/9/2014)


    Hi,

    I created an ETL process using C# (SSIS was not available) and ran into some issues with one of the fields.

    I have a [description] that contains data similar to this "Total for customer 000999 ACME INDUSTRIAL ". I want to extract the customer number (in this case) 00999 (notice I dropped one zero) load (update this data to a customer number field within the same table). Extract the ACME INDUSTRIAL to the customer name field within the same table. I've tried using Substring with CHARINDEX and other string functions. How can I extract this data? It seems straightforward but I'm getting stuck. Any help would be appreciated.

    Regards,

    Note this data for this description field is consistent and the source can't be changed on the customer's side.

    If you were to remove "Total for customer " from the string, would you be left with the customer number followed by a space, then the customer name? If it's more complex than this, please provide a few examples.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi,

    Once you said remove the values it hit me.

    This is what I came up with:

    // Move the data to the new field preserving original values

    UPDATE dbo.StagingTable

    SET customer = [Description]

    WHERE [description] LIKE '%Total%'

    AND processed = 1

    // Replace unnecessary data step 1

    UPDATE dbo.StagingTable

    SET customer = RTRIM(LTRIM(REPLACE(customer, '"Total for customer 0', '')))

    WHERE [description] LIKE '%Total%'

    AND processed = 1

    // Replace unnecessary data step 2

    UPDATE dbo.StagingTable

    SET customer = RTRIM(LTRIM(REPLACE(customer, '"', '')))

    WHERE [description] LIKE '%Total%'

    AND processed = 1

    // Extract and Update the zone code to ZoneLoc

    UPDATE dbo.StagingTable

    SET ZoneLoc = LEFT(customer, CHARINDEX(' ', customer))

    WHERE [description] LIKE '%total%'

    AND processed = 1

    // Update the customer field with the customer name

    UPDATE dbo.StagingTable

    SET customer = RIGHT(customer,

    LEN([customer]) - CHARINDEX(' ', [customer], 1))

    WHERE [Description] LIKE '%total%'

    AND processed = 1

    Results:

    Before: " Total For Customer 000999 ACME COMPANY"

    After:

    Customer: Acme Company

    ZoneLoc: 00999

    Regards,

  • If the customer number is always 6 numeric characters immediately followed by the customer name and the string is surrounded in double quotes with the end double quote separated from the company name by a space as shown in the single sample provided in your initial post, then the following would also work:

    declare @TestStr varchar(128) = '"Total for customer 000999 ACME INDUSTRIAL "';

    select

    substring(@TestStr,patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%',@TestStr) + 1, 5),

    substring(@TestStr,patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%',@TestStr) + 7, patindex('%_"', @TestStr) - (patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%',@TestStr) + 6))

Viewing 6 posts - 1 through 5 (of 5 total)

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