Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 26th, 2004, 04:30 PM
Registered User
 
Join Date: Jul 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL String Parsing

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
 
Old July 26th, 2004, 05:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 26th, 2004, 10:47 PM
Registered User
 
Join Date: Jul 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you both for your answers.

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

Thanks again.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing from a text string jroxit Classic ASP Basics 5 November 18th, 2008 05:08 PM
Parsing of String jmss66 VB How-To 9 May 1st, 2008 12:47 AM
Parsing a String? MBowen SQL Server 2000 9 December 1st, 2006 01:17 PM
Parsing a text string medix_911 Access 8 November 21st, 2006 01:37 PM
Parsing a string of numbers tp194 Classic ASP Databases 1 January 5th, 2004 10:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.