Solved

# In MS SQL parsing a field delimited with '-'

Posted on 2009-04-29
236 Views
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
Question by:n2dweb

LVL 41

Expert Comment

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

LVL 41

Expert Comment

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

LVL 41

Accepted Solution

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

LVL 1

Author Closing Comment

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

LVL 1

Author Comment

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

LVL 41

Expert Comment

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

### Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦