extract string between underscore

• lkeswar

  SSC Enthusiast

  Points: 190

  More actions

  December 3, 2017 at 10:50 am

  #324880

  Hi,
    We have a requirement to extract string between 4th underscore and data as follows.
     BA6FC89_78345_CET_12345_16890_87651010
     CV6789_24356_NBHY_3456_178901_834301481
    Output should be like as ;
     16890
     178901

    Please help me in advance

  Thx,

  .

• HappyGeek

  SSCoach

  Points: 18726

  More actions

  December 3, 2017 at 11:07 am

  #1970433

  Check out the string splitter function here

  ...

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  December 5, 2017 at 2:07 pm

  #1970804

  
DECLARE @t table(Id int IDENTITY,textval varchar(255));
INSERT @t ( textval ) VALUES
    ( 'BA6FC89_78345_CET_12345_16890_87651010' )
  , ('CV6789_24356_NBHY_3456_178901_834301481');
WITH
   n1 AS (SELECT * FROM (VALUES(0),(1)) t(n))
 , n2 AS (SELECT n1.n FROM n1 CROSS JOIN n1 n)
 , n3 AS (SELECT n2.n FROM n2 CROSS JOIN n2 n) 
 , n4 AS (SELECT n3.n FROM n3 CROSS JOIN n3 n) 
 , n5 AS (SELECT n4.n FROM n4 CROSS JOIN n4 n)
 , n  AS (SELECT Row_Number() OVER (ORDER BY n ) n FROM n4 )
 , r  AS (SELECT Id,  SUBSTRING(textval, n+1, CHARINDEX(Char(95), Concat(textval,Char(95)), n+1)-n-1) Word, o=n
         FROM n
         JOIN @t t ON Substring(textval, n, 1) = Char(95)
         WHERE n BETWEEN 1 AND  Len(t.textval)
         )
  , result AS
         (
         SELECT  r.Id, r.Word, t.textval, Row_Number() OVER (PARTITION BY r.Id ORDER BY o) WordOrder
         FROM r
         JOIN @t t ON r.Id = t.Id
         ORDER BY r.Id, o OFFSET 1 ROW FETCH NEXT 20 ROWS ONLY
         )
SELECT result.Id
     , result.Word
     , result.textval
     , result.WordOrder
FROM result
WHERE result.WordOrder=4
ORDER BY result.Id, result.WordOrder;

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  December 5, 2017 at 5:11 pm

  #1970819

  Joe Torre - Tuesday, December 5, 2017 2:07 PM

  
DECLARE @t table(Id int IDENTITY,textval varchar(255));
INSERT @t ( textval ) VALUES
    ( 'BA6FC89_78345_CET_12345_16890_87651010' )
  , ('CV6789_24356_NBHY_3456_178901_834301481');
WITH
   n1 AS (SELECT * FROM (VALUES(0),(1)) t(n))
 , n2 AS (SELECT n1.n FROM n1 CROSS JOIN n1 n)
 , n3 AS (SELECT n2.n FROM n2 CROSS JOIN n2 n) 
 , n4 AS (SELECT n3.n FROM n3 CROSS JOIN n3 n) 
 , n5 AS (SELECT n4.n FROM n4 CROSS JOIN n4 n)
 , n  AS (SELECT Row_Number() OVER (ORDER BY n ) n FROM n4 )
 , r  AS (SELECT Id,  SUBSTRING(textval, n+1, CHARINDEX(Char(95), Concat(textval,Char(95)), n+1)-n-1) Word, o=n
         FROM n
         JOIN @t t ON Substring(textval, n, 1) = Char(95)
         WHERE n BETWEEN 1 AND  Len(t.textval)
         )
  , result AS
         (
         SELECT  r.Id, r.Word, t.textval, Row_Number() OVER (PARTITION BY r.Id ORDER BY o) WordOrder
         FROM r
         JOIN @t t ON r.Id = t.Id
         ORDER BY r.Id, o OFFSET 1 ROW FETCH NEXT 20 ROWS ONLY
         )
SELECT result.Id
     , result.Word
     , result.textval
     , result.WordOrder
FROM result
WHERE result.WordOrder=4
ORDER BY result.Id, result.WordOrder;

  Careful, Joe.  Concatenation of a trailing delimiter is what makes a lot of splitters slow.  Even the original DelimitedSplit8K function had that problem.

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