asked on

In MS SQL parsing a field delimited with '-'

I need to parse a field named cID that is delimited by a '-' to return 4 parts, Sample data in the field cID looks like:
DT-4-G-A
2A16V-6A-BBBB-654XX
10010-80-128-0
10010-80-128-2
DT-O-G-1

I will need to use this in the WHERE clause as well
WHERE partA = 'DT' OR partD = '0'

Pavel Celba

Let suppose there are four parts always.

The first part:
LEFT(cID, CHARINDEX('-',cID)-1)

The last part:
RIGHT(RTRIM(cID), CHARINDEX('-',REVERSE(RTRIM(cID)))-1)

The second part:
SUBSTRING(cID, CHARINDEX('-',cID)+1, CHARINDEX('-',cID,CHARINDEX('-',cID)+1)-CHARINDEX('-',cID)-1)

The third part:
I think you should think to add four more columns to your table ... :-)

Pavel Celba

The third part can be derived from the second part in reverse string but it seems to be too complex...

REVERSE(SUBSTRING(REVERSE(cID), CHARINDEX('-',REVERSE(cID))+1, CHARINDEX('-',REVERSE(cID),CHARINDEX('-',REVERSE(cID))+1)-CHARINDEX('-',REVERSE(cID))-1))

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

n2dweb

ASKER

I will create functions to return the parts for a given record -
But I may just simply change the table and add more fields
thanks

n2dweb

ASKER

Here is part3
LEFT(SUBSTRING(cID, CHARINDEX('-', cID, CHARINDEX('-', cID) + 1) + 1, LEN(cID) - CHARINDEX('-', cID) + 1), CHARINDEX('-', SUBSTRING(cID, CHARINDEX('-', cID, CHARINDEX('-', cID) + 1) + 1, LEN(cID) - CHARINDEX('-', cID) + 1)) - 1) AS part3

Below is the entire Select statement
SELECT cID,
LEFT(cID, CHARINDEX('-', cID) - 1) AS Part1,
SUBSTRING(cID, CHARINDEX('-', cID) + 1, CHARINDEX('-', cID, CHARINDEX('-', cID) + 1) - CHARINDEX('-', cID) - 1) AS Part2,
LEFT(SUBSTRING(cID, CHARINDEX('-', cID, CHARINDEX('-', cID) + 1) + 1, LEN(cID) - CHARINDEX('-', cID) + 1), CHARINDEX('-', SUBSTRING(cID, CHARINDEX('-', cID, CHARINDEX('-', cID) + 1) + 1, LEN(cID) - CHARINDEX('-', cID) + 1)) - 1) AS part3,
RIGHT(RTRIM(cID), CHARINDEX('-', REVERSE(RTRIM(cID))) - 1) AS pART4
FROM dbo.XXX

Pavel Celba

I would say the part3 with REVERSE is a little bit simpler :-)

BTW, Visual FoxPro allows following syntax:
Part1 = WORDNUM(cID, 1, "-")
Part2 = WORDNUM(cID, 2, "-")
Part3 = WORDNUM(cID, 3, "-")
Part4 = WORDNUM(cID, 4, "-")