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
  • Last Modified:

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
Asked:
n2dweb
  • 4
  • 2
1 Solution
 
pcelbaCommented:
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
 
pcelbaCommented:
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
 
pcelbaCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
n2dwebprogrammerAuthor 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
 
n2dwebprogrammerAuthor 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
 
pcelbaCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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