SQL String Parsing

Transact-SQL
,
1

Hi,

How can I get the out below? I tried this SQL script but it's now working. Any help is appreciated.

SELECT PersonID,
[ProductCode] = SUBSTRING(ProductCode,CHARINDEX('/',ProductCode,
CHARINDEX('/',ProductCode, CHARINDEX('/',ProductCode,
CHARINDEX('/',ProductCode, CHARINDEX('/',ProductCode,
CHARINDEX('/',ProductCode)+1)+1)+1)+1) + 1)+1,
DATALENGTH(ProductCode) - CHARINDEX('/',ProductCode,
CHARINDEX('/',ProductCode)+1) -
CHARINDEX('/',REVERSE(ProductCode))-1 -
CHARINDEX('/',REVERSE(ProductCode))-1 -
CHARINDEX('/',REVERSE(ProductCode))-1)
FROM Temp_Product

CREATE TABLE Temp_Product (
PersonID int,
ProductCode varchar(256),

);
INSERT INTO Temp_Product (PersonID, ProductCode)
VALUES ('234', 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'),
('567', 'Organitation/My Companies/company ABC/My company divisoin 34/my company place 23/department/place of work 433/678/58asu/sdfd45679'),
('876', 'Organitation/My Companies/company ABC/My company divisoin 56/my company place 54/department/place of work 7654/455/68as/sdvd45679'),
('543', 'Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215')
('643', 'Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215');

Output:
234,place of work 234
567, place of work 433
876,place of work 7654
543,place of work 876
643,place of work 876

2

You might consider DelimitedSplit8K by @JeffModen.
Or try this:

with cte
  as (select personid
            ,1 as levelno
            ,reverse(productcode) as productcode
            ,1 as idx
            ,charindex('/',reverse(productcode))-1 as idx_len
        from temp_product
      union all
      select personid
            ,levelno+1 as levelno
            ,productcode
            ,idx+idx_len+1 as idx
            ,charindex('/',productcode,idx_len+idx+1)-idx-idx_len-1 as idx_len
        from cte
       where charindex('/',productcode,idx_len+idx+1)>0
         and levelno<4
     )
select personid
      ,reverse(substring(productcode,idx,idx_len)) as productcode
  from cte as a
 where levelno=4
 order by a.personid
         ,a.levelno
;
1 Like
3

Hi,

Thanks you. This helped solved the issue.

4

While a tally based string splitter wold certainly work, it's probably overkill (an an unessary expense)for something like this.

For a situation like this, I'd recommend using the CROSS APPLY VALUES method to simplify the code.

IF OBJECT_ID('tempdb..#Temp_Product', 'U') IS NOT NULL 
DROP TABLE #Temp_Product;
GO

CREATE TABLE #Temp_Product (
PersonID int,
ProductCode varchar(256),

);
INSERT INTO #Temp_Product (PersonID, ProductCode)
VALUES ('234', 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'),
('567', 'Organitation/My Companies/company ABC/My company divisoin 34/my company place 23/department/place of work 433/678/58asu/sdfd45679'),
('876', 'Organitation/My Companies/company ABC/My company divisoin 56/my company place 54/department/place of work 7654/455/68as/sdvd45679'),
('543', 'Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215'),
('643', 'Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215');

SELECT 
	tp.PersonID, 
	PlaceOfWork = SUBSTRING(tp.ProductCode, sl.SecondLocation + 1, tl.ThirdLocation - sl.SecondLocation - 1)
FROM 
	#Temp_Product tp
	CROSS APPLY ( VALUES (CHARINDEX('/', tp.ProductCode, 1)) ) fl (FirstLocation)
	CROSS APPLY ( VALUES (CHARINDEX('/', tp.ProductCode, fl.FirstLocation + 1)) ) sl (SecondLocation)
	CROSS APPLY ( VALUES (CHARINDEX('/', tp.ProductCode, sl.SecondLocation + 1)) ) tl (ThirdLocation);