-1

I have a string coming like '1234_XXXX_RHL_PQR' and in output I want to pull character coming after 'XXXX_' that is 'RHL'.

I would always have 'XXXX' in string and my task is to get string after 'XXXX_'

rory.ap
  • 34,009
  • 10
  • 83
  • 174
Rahul Sevani
  • 77
  • 12
  • 1
    Minus one for no research effort. – rory.ap May 21 '18 at 11:18
  • Possible duplicate of [SQL Server 2014: Regex to substring](https://stackoverflow.com/questions/41104443/sql-server-2014-regex-to-substring) – Peter Abolins May 21 '18 at 11:23
  • Is XXXX ALWAYS the second value? And is there any chance you can stop storing delimited values in the first place? That is the cause of your issue. Delimited data violates 1NF and causes incredible amounts of anguish. – Sean Lange May 21 '18 at 13:36
  • @Rahul sevani Hii I added one more answer you can check it out – Sreenu131 May 21 '18 at 15:29

4 Answers4

1

Try This

DECLARE @Table AS TABLE (Data Nvarchar(100))
INSERT INTO @Table
SELECT '1234_XXXX_RHL_PQR'

SELECT Data,CAST('<S>'+REPLACE(Data,'_','</S><S>')+'</S>' AS XML).value('/S[3]','nvarchar(1000)') AS ReqData

FROM @Table

Result

Data                ReqData
----------------------------
1234_XXXX_RHL_PQR   RHL

DEMO : http://rextester.com/PXHSDZ80426

Sreenu131
  • 2,476
  • 1
  • 7
  • 18
  • One more que, cant we use any String Split function. Because if in this case I'm always expecting 'XXXX_' in second place. But in actual scenario I may get 'XXXX_' in any place and I would like to get string just after that ??? – Rahul Sevani May 21 '18 at 11:51
0

Try the following:

select 
right(yourfield,len(yourfield)-3-patindex('%XXXX%',yourfield))
from yourtable
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
0

I would use substring() with charindex() function 

select col, substring(col, charindex('XXXX_', col)+5, len(col)) as Ncol
from table t;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

First Create Table-Value-Function

CREATE FUNCTION [dbo].[Udf_GetReqString](@IputData Varchar(200))
RETURNS @OutTable TABLE
(
ActualData varchar(200),
ReqData varchar(200)
)
AS
BEGIN
    DECLARE @Table AS TABLE (Data Nvarchar(100))
    INSERT INTO @Table
    SELECT @IputData

    INSERT INTO @OutTable
    SELECT Data,SUBSTRING(DatReq,-1,CHARINDEX('_',Data )) AS DatReq
    FROM
    (
    SELECT Data,SUBSTRING(Data,CHARINDEX('X_',Data )+2,LEN(Data) )  AS DatReq
    FROM @Table
    )dt
RETURN
END

Sample And Call Function

DECLARE @Table AS TABLE (Data Nvarchar(100))
INSERT INTO @Table
SELECT '1234_XXXX_SHL_PQR' UNION ALL
SELECT '1234_XXXX_RHL_PQR' UNION ALL
SELECT '1234_DDHDFD_XXXX_PHL_PQR' UNION ALL
SELECT '1234_ABAD_DADADA_XXXX_GHL_PQR'


SELECT t.Data,
        udf.ReqData 
FROM @Table t
CROSS APPLY [dbo].[Udf_GetReqString]  (t.Data) As udf

Result

Data                            ReqData
-------------------------------------------
1234_XXXX_SHL_PQR               SHL
1234_XXXX_RHL_PQR               RHL
1234_DDHDFD_XXXX_PHL_PQR        PHL
1234_ABAD_DADADA_XXXX_GHL_PQR   GHL

DEMO http://sqlfiddle.com/#!18/2bcfa/1

Sreenu131
  • 2,476
  • 1
  • 7
  • 18