SQL String Parsing

TdyIndy · July 26th, 2004, 04:30 PM

Hello, everyone. I'm a newbie, and totally stumped here. I was wondering if anyone can help me with the following:

I have a table in which demographic data is delimited by pipes "|" within a field. I need to be able to extract an PartID number from the field, so the record may exist as PartID|123456789|CustomerID|888888 or it may exist as PartID||CustomerID|88888|. The PartID can be any length.

I need to be able to generate a query that will pick out the value between the PartID and the CustomerID. If the value is NULL, it should return a NULL value.

I can't change the design of the table, so I have to work with what I have. I have this so far:

SELECT DISTINCT SUBSTRING(customer_info, (SELECT patindex('%PartID|%' , customer_info)AS POSITION ) +7, 9)
FROM dbo.customer_info
WHERE (customer_info LIKE '%PartID|%')

But I can't figure out the next step. I am using SQL Server.

Any suggestions? Thank you in advance.

Tim

happygv · July 26th, 2004, 05:12 PM

Hi there,

Use this. Not sure if your TableName and ColumnName are both called Customer_Info

Code:

select PartId = case 
    When SUBSTRING(customer_info, charindex('PartId|',customer_info)+7, charindex('|CustomerId|',customer_info)-8) = '' Then NULL
    Else SUBSTRING(customer_info, charindex('PartId|',customer_info)+7, charindex('|CustomerId|',customer_info)-8)
    End
from dbo.customer_info

Cheers!

_________________________
- Vijay G
Strive for Perfection

TdyIndy · July 26th, 2004, 10:47 PM

Thank you both for your answers.

And Jeff, I couldn't agree with you more.

Thanks again.