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_'
-
1Minus one for no research effort.rory.ap– rory.ap2018-05-21 11:18:12 +00:00Commented May 21, 2018 at 11:18
-
Possible duplicate of SQL Server 2014: Regex to substringPeter Abolins– Peter Abolins2018-05-21 11:23:38 +00:00Commented May 21, 2018 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– Sean Lange2018-05-21 13:36:10 +00:00Commented May 21, 2018 at 13:36
-
@Rahul sevani Hii I added one more answer you can check it outSreenu131– Sreenu1312018-05-21 15:29:44 +00:00Commented May 21, 2018 at 15:29
Add a comment
|
4 Answers
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
1 Comment
Rahul Sevani
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 ???
Try the following:
select
right(yourfield,len(yourfield)-3-patindex('%XXXX%',yourfield))
from yourtable
Comments
I would use substring() with charindex() function
select col, substring(col, charindex('XXXX_', col)+5, len(col)) as Ncol
from table t;
Comments
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