Parse SQL String

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  June 15, 2017 at 8:42 pm

  #337669

  Hi,
  What's the best way to only get "place of work 234" from the Product code?  I tried the sql below but it's not working.
  
DECLARE @ProductCode VARCHAR(256)

  SET @ProductCode = 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'

  SELECT
   [Part1] = 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)


• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 15, 2017 at 11:47 pm

  #1947047

  You can use the DelimitedSplit8K or the DelimitedSplit8K_LEAD functions
  😎

  DECLARE @ProductCode VARCHAR(256) = 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'

  SELECT
    DPARTS.Item
FROM    dbo.DelimitedSplit8K_LEAD(@ProductCode,CHAR(47))    DPARTS
WHERE    DPARTS.ItemNumber    = 7;

  

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  June 16, 2017 at 4:34 am

  #1947067

  HI

  Thanks for the reply.  Is it possible to do this without creating a function?

  Thanks,

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  June 16, 2017 at 5:31 am

  #1947078

  ejbatu1 - Friday, June 16, 2017 4:34 AM

  HI

  Thanks for the reply.  Is it possible to do this without creating a function?

  Thanks,

  Yes, but why would you want to?  The function encapsulates all the logic you need and can be used elsewhere.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  June 16, 2017 at 6:34 am

  #1947091

  The reason is that, I would also like to use this script in SQL Server 2008 and LEAD() function is not available in SQL 2008.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 16, 2017 at 6:38 am

  #1947094

  ejbatu1 - Friday, June 16, 2017 4:34 AM

  HI

  Thanks for the reply.  Is it possible to do this without creating a function?

  Thanks,

  Here is one way of doing this, should be quite efficient by the looks of the execution plan 😉
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @ProductCode VARCHAR(256) = 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@ProductCode)) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4)
,DELIM_POS AS
(
  SELECT
   ROW_NUMBER() OVER
    (
      ORDER BY @@VERSION
    ) AS PRT
  ,NM.N
  FROM  NUMS  NM
  WHERE ASCII(SUBSTRING(@ProductCode,NM.N,1) ) = 47
)
SELECT
 SUBSTRING(@ProductCode,DP.N + 1,(CHARINDEX(CHAR(47),@ProductCode,DP.N + 1) - (DP.N + 1))) AS ITEM7
FROM  DELIM_POS   DP
WHERE DP.PRT  = 6;


• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 16, 2017 at 6:40 am

  #1947095

  ejbatu1 - Friday, June 16, 2017 6:34 AM

  The reason is that, I would also like to use this script in SQL Server 2008 and LEAD() function is not available in SQL 2008.

  Then next time post questions on the appropriate forum, this one is the 2012. We are very good in SQL but no good in ESP
  😎

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 16, 2017 at 6:55 am

  #1947096

  Since we are on the 2012 forum, here is a LEAD version
  😎
  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @ProductCode VARCHAR(256) = 'Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679'

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@ProductCode)) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4)
,DELIM_POS AS
(
  SELECT
   ROW_NUMBER() OVER
    (
      ORDER BY @@VERSION
    ) AS PRT
  ,NM.N
  FROM  NUMS  NM
  WHERE ASCII(SUBSTRING(@ProductCode,NM.N,1) ) = 47
)
,STRING_PARTS AS
(
  SELECT
   DP.PRT
  ,SUBSTRING(@ProductCode,DP.N + 1,LEAD(DP.N,1,DP.N) OVER (ORDER BY DP.PRT)-(DP.N + 1)) AS PART
  FROM  DELIM_POS   DP
)
SELECT
  SP.PART
FROM  STRING_PARTS  SP
WHERE SP.PRT  = 6;

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  June 16, 2017 at 7:54 am

  #1947102

  Eirikur Eiriksson - Friday, June 16, 2017 6:40 AM

  ejbatu1 - Friday, June 16, 2017 6:34 AM

  The reason is that, I would also like to use this script in SQL Server 2008 and LEAD() function is not available in SQL 2008.

  Then next time post questions on the appropriate forum, this one is the 2012. We are very good in SQL but no good in ESP
  😎

  Amen to that.

  SQL 2008 does support functions.  DelimitedSplit8K works on 2008 and 2005.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  June 17, 2017 at 7:59 am

  #1947173

  Thank you for your help on this, but I'm having a hard time using the script... please don't kill me.  How would I use it if @ProductCode is from a database table where the value is different for each person?
  For example:
  
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');


• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 18, 2017 at 5:38 am

  #1947186

  dEVafri - Saturday, June 17, 2017 7:59 AM

  Thank you for your help on this, but I'm having a hard time using the script... please don't kill me.  How would I use it if @ProductCode is from a database table where the value is different for each person?
  For example:
  
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');


  This should get you passed this hurdle
  😎
  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.Temp_Product') IS NOT NULL DROP TABLE dbo.Temp_Product;
CREATE TABLE dbo.Temp_Product
(
PersonID int,
ProductCode varchar(256),
);
INSERT INTO dbo.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');

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
,PARSED_STRING AS
(
  SELECT
   TP.PersonID
   ,ROW_NUMBER() OVER
    (
     PARTITION BY TP.PersonID
     ORDER BY @@VERSION
    ) AS PRT
   ,NM.N
   ,TP.ProductCode
  FROM  dbo.Temp_Product  TP
  CROSS APPLY (SELECT TOP(LEN(TP.ProductCode)) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4) NM
  WHERE ASCII(SUBSTRING(TP.ProductCode,NM.N,1) ) = 47
)
SELECT
  PS.PersonID
 ,SUBSTRING(PS.ProductCode,PS.N + 1,(CHARINDEX(CHAR(47),PS.ProductCode,PS.N + 1) - (PS.N + 1))) AS ITEM7
FROM  PARSED_STRING PS
WHERE PS.PRT = 6;

• Jeff Moden

  SSC Guru

  Points: 1004378

  More actions

  June 18, 2017 at 2:32 pm

  #1947198

  dEVafri - Friday, June 16, 2017 6:34 AM

  The reason is that, I would also like to use this script in SQL Server 2008 and LEAD() function is not available in SQL 2008.

  So use the DelimitedSplit8K method, which doesn't use LEAD and is still faster than any other method other than a CLR or the LEAD version.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 20, 2017 at 9:54 am

  #1947409

  dEVafri - Saturday, June 17, 2017 7:59 AM

  Thank you for your help on this, but I'm having a hard time using the script... please don't kill me.  How would I use it if @ProductCode is from a database table where the value is different for each person?
  For example:
  
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');


  Try using the function this way:
  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');

  SELECT TP.PersonID, S.Item AS ProductCode
FROM Temp_Product AS TP
    CROSS APPLY dbo.DelimitedSplit8K(TP.ProductCode, '/') AS S
WHERE S.Item LIKE 'place of work%';

  DROP TABLE Temp_Product;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 20, 2017 at 10:20 am

  #1947418

  i created this simple parsename function that grabs items left to right. 
  so if the item you want is always int eh seventh split, this works great:
  /*
PersonID ProductCode                                      Item
234    Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679   place of work 234
567    Organitation/My Companies/company ABC/My company divisoin 34/my company place 23/department/place of work 433/678/58asu/sdfd45679 place of work 433
876    Organitation/My Companies/company ABC/My company divisoin 56/my company place 54/department/place of work 7654/455/68as/sdvd45679 place of work 7654
543    Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215 place of work 876
*/
select * from Temp_Product
cross apply master.dbo.fn_parsename(ProductCode,'/',7)

  IF OBJECT_ID('[dbo].[fn_parsename]') IS NOT NULL
DROP FUNCTION [dbo].[fn_parsename]
GO
CREATE FUNCTION dbo.fn_parsename
   (
   @pString  VARCHAR(7999),
   @pDelimiter CHAR(1),
   @Occurrance int
   )
RETURNS table with schemabinding
AS

  return
--===== "Inline" CTE Driven "Tally Table” produces values up to
  -- 10,000... enough to cover VARCHAR(8000)
WITH
  E1(N) AS ( --=== Create Ten 1's
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 --10
     ),
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,
--===== Do the split
InterResults
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
   SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
 FROM cteTally
WHERE N < LEN(@pString) + 2
  AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
)
SELECT Item FROM InterResults WHERE ItemNumber = @Occurrance

  GO


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 20, 2017 at 12:44 pm

  #1947446

  Lowell - Tuesday, June 20, 2017 10:20 AM

  i created this simple parsename function that grabs items left to right. 
  so if the item you want is always int eh seventh split, this works great:
  /*
PersonID ProductCode                                      Item
234    Organitation/My Companies/company ABC/My company divisoin/my company place/department/place of work 234/678/98as/sdfd45679   place of work 234
567    Organitation/My Companies/company ABC/My company divisoin 34/my company place 23/department/place of work 433/678/58asu/sdfd45679 place of work 433
876    Organitation/My Companies/company ABC/My company divisoin 56/my company place 54/department/place of work 7654/455/68as/sdvd45679 place of work 7654
543    Organitation/My Companies/company ABC/My company divisoin 21/my company place 12/department/place of work 876/876/93af/sdxd453215 place of work 876
*/
select * from Temp_Product
cross apply master.dbo.fn_parsename(ProductCode,'/',7)

  IF OBJECT_ID('[dbo].[fn_parsename]') IS NOT NULL
DROP FUNCTION [dbo].[fn_parsename]
GO
CREATE FUNCTION dbo.fn_parsename
   (
   @pString  VARCHAR(7999),
   @pDelimiter CHAR(1),
   @Occurrance int
   )
RETURNS table with schemabinding
AS

  return
--===== "Inline" CTE Driven "Tally Table†produces values up to
  -- 10,000... enough to cover VARCHAR(8000)
WITH
  E1(N) AS ( --=== Create Ten 1's
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 --10
     ),
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,
--===== Do the split
InterResults
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
   SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
 FROM cteTally
WHERE N < LEN(@pString) + 2
  AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
)
SELECT Item FROM InterResults WHERE ItemNumber = @Occurrance

  GO


  Lowell, look into changing the inline CTE construct, this will shave of some CPU cycles
  😎

  
  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
  , NUMS(N) AS (SELECT TOP(LEN(@MY_STR)) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)