-3

How I can extract the substring of a string from end to beginning?

I have the following text, and I need extract the last "Modificado Por" and the "Creado Por"

/* CREADO POR           :   Wxxwww wwwwww                                                   */
/* FECHA CREACIÓN       :   10/12/2015                                                      */
/* DESCRIPCIÓN          :                                                                   */
/* ======================================================================================== */ 
/* MODIFICADO POR       :   Xxxxx xxxxxxxxxxx                                               */
/* FECHA CREACIÓN       :   03/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxx  Xxxxxxxxx                                               */
/* FECHA CREACIÓN       :   30/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxxxx xxxxxxx                                                */
/* FECHA CREACIÓN       :   05/04/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
  • 1
    What exactly do you need the output to be? Is this happening against one value or does it need to happen against multiple values in a table? WHat happens if there is no CREADO POR or MODIFICADO POR? – Aaron Bertrand May 24 '16 at 16:48
  • I need the output last "modificado por" of the list. "Creador por" and "Modificado Por" always exist. Thanks! – Diego Flores May 24 '16 at 17:04
  • 1
    Can you show in the question what you mean by I need the output last "modificado por" of the list.? You haven't added any new information, you've just re-worded the same broad requirement. – Aaron Bertrand May 24 '16 at 17:12
  • Aaron, excuse my poor english. I need show only the last line "MODIFICADO POR : Xxxxxxxx xxxxxxx " of the all text. In this case the line of the paragraph with date or FECHA CREACIÓN : 05/04/2016 – Diego Flores May 24 '16 at 17:21
  • @diegoxfs Please update the Question with the exact output you expect given the input you have already provided. You also have three slightly different descriptions: "I need extract the last "Modificado Por" and the "Creado Por"" and "I need the output last "modificado por"" and "I need show only the last line "MODIFICADO POR : Xxxxxxxx xxxxxxx " of the all text."? Which one is it? Please update the Question to be very specific, especially since "FECHA CREACIÓN : 05/04/2016" is not the last line; "DESCRIPCIÓN :" is the last line. – Solomon Rutzky May 24 '16 at 18:30
1

I didn't spend a lot of time on performance with this one, but assuming you are ready in some kind of header info, this code should work for you.

  1. Parse the string out lines based on a line feed (dump to temp table)
  2. Find the largest line with the pattern you are looking for.
  3. Read the string from the previous end line position.

Code:

DECLARE @procHeader NVARCHAR(MAX);
DECLARE @strPattern NVARCHAR(MAX);

SET @procHeader=N'/* CREADO POR           :   Wxxwww wwwwww                                                   */
/* FECHA CREACIÓN       :   10/12/2015                                                      */
/* DESCRIPCIÓN          :                                                                   */
/* ======================================================================================== */ 
/* MODIFICADO POR       :   Xxxxx xxxxxxxxxxx                                               */
/* FECHA CREACIÓN       :   03/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxx  Xxxxxxxxx                                               */
/* FECHA CREACIÓN       :   30/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxxxx xxxxxxx                                                */
/* FECHA CREACIÓN       :   05/04/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */';

SET @strPattern=N'%MODIFICADO POR%';

WITH cte_LineFeeds AS
(
    SELECT 1 LineNum, 0 PrevLineNum, CHARINDEX(char(10),@procHeader) LineEndPosition
    UNION ALL
    SELECT LineNum+1, PrevLineNum+1, CHARINDEX(char(10),@procHeader, LineEndPosition+1)
    FROM cte_LineFeeds
    WHERE LineEndPosition > 0 and LineEndPosition < LEN(@procHeader)
)
SELECT * INTO #TEMP
FROM cte_LineFeeds;

SELECT SUBSTRING(@procHeader,LineEndPosition+1,LEN(@procHeader))
FROM  #TEMP
WHERE LineNum = (
    selSELECTect  max(l1.LineNum)
    FROM #TEMP l1
        LEFT JOIN #TEMP l2 ON l2.PrevLineNum=l1.LineNum
    WHERE SUBSTRING(@procHeader,l1.LineEndPosition+1,l2.LineEndPosition) like @strPattern);
3

Well, I did something similar to Steve, but with no need for a #temp table or recursive CTE.

DECLARE @BigString nvarchar(max) = N'
/* CREADO POR           :   Wxxwww wwwwww                                                   */
/* FECHA CREACIÓN       :   10/12/2015                                                      */
/* DESCRIPCIÓN          :                                                                   */
/* ======================================================================================== */ 
/* MODIFICADO POR       :   Xxxxx xxxxxxxxxxx                                               */
/* FECHA CREACIÓN       :   03/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxx  Xxxxxxxxx                                               */
/* FECHA CREACIÓN       :   30/03/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */
/* MODIFICADO POR       :   Xxxxxxxx xxxxxxx                                                */
/* FECHA CREACIÓN       :   05/04/2016                                                      */
/* DESCRIPCIÓN          :                                                                   */
/*                                                                                          */
/* ======================================================================================== */';

;WITH x(c, m) AS 
(
  SELECT RIGHT(@BigString, CHARINDEX(REVERSE(N'CREADO POR'),     REVERSE(@BigString))),
         RIGHT(@BigString, CHARINDEX(REVERSE(N'MODIFICADO POR'), REVERSE(@BigString)))
),
y(c, m) AS 
(
  SELECT SUBSTRING(c, CHARINDEX(N'FECHA CREACIÓN', c), 4000),
         SUBSTRING(m, CHARINDEX(N'FECHA CREACIÓN', m), 4000)
  FROM x
)
SELECT 
  creado     = LEFT(c, CHARINDEX(N'*/', c) - 1), 
  modificado = LEFT(m, CHARINDEX(N'*/', m) - 1)
FROM y;

As an aside, these things are clearly relational in nature. So you really should be storing them relationally. For example, a row for each creation / modification event, as opposed to some big wall of text you have to parse every time.

  • I've never seen the REVERSE function. That's pretty cool! I only used the temp table because I was doing a recursive CTE and didn't want it executing multiple times. @diegoxfs, you should mark this as the more correct answer. I've had to do searches like this in the past to parse out info in proc headers. I assumed that's what this was for. – Steve Mangiameli May 24 '16 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.