Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 306

# 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'
0
n2dweb
• 4
• 2
1 Solution

Commented:
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 ... :-)

0

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

0

Commented:
It is possible to use above expressions in WHERE but to create a view with four calculated columns would siplify your SELECTs a lot.

CREATE VIEW YourView AS
SELECT *, LEFT(cID, CHARINDEX('-',cID)-1) AS PartA,
SUBSTRING(cID, CHARINDEX('-',cID)+1, CHARINDEX('-',cID,CHARINDEX('-',cID)+1)-CHARINDEX('-',cID)-1) AS PartB,
etc.
FROM YourTable

0

programmerAuthor Commented:
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
0

programmerAuthor Commented:
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
0

Commented:
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, "-")
0

## Featured Post

• 4
• 2
Tackle projects and never again get stuck behind a technical roadblock.