Parsing strings from a field value

Question

Parsing strings from a field value

Joe Contreras-290946

SSCommitted

Points: 1903
More actions
July 9, 2014 at 5:58 am

#287791

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.

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

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

CKX SSCrazy Points: 2519 More actions · Answer 1

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?

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 4

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,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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))