How to extract a specific string in SQL Server?

Question

I have an NVARCHAR string in SQL Server 2012 like so:

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"

I want to get the value after SiteCode=" (i.e. 'LA123').

I'm trying to use SUBSTRING with CHARINDEX but no luck (like whats suggested here).

I'm sure there is an easy way to do this?

SUBSTRING with CHARINDEX sounds like a viable solution. Please detail what you have tried and what results you're expecting. — Paul Hunt, Feb 01 '16 at 09:03
You can try right() function... MSDN is [here](https://msdn.microsoft.com/en-IN/library/ms177532.aspx) — Akshay Gupta, Feb 01 '16 at 09:04
Smells like bad design to me. different data should be stored in different columns. — Zohar Peled, Feb 01 '16 at 09:10
@AkshayGupta RIGHT is not that useful to find a text inside a text, and Ashes already showed that he tried to use SUBSTRING and CHARINDEX — t-clausen.dk, Feb 01 '16 at 09:22
answers below are not working well, because when your string will be dynamic, for example `'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123" xqwxqxqxqxq dqw qdqw qw d'` the solutions won't work — krtek, Feb 01 '16 at 09:23

score 1 · Answer 1 · answered Feb 01 '16 at 09:11

1

Here is the way:

DECLARE @syncData AS nvarchar(4000), @vSiteCodeStartIndex AS INT, @vSiteCodeEndIndex AS INT;
SET @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"';

SELECT @vSiteCodeStartIndex = CHARINDEX('SiteCode="',@syncData)+LEN('SiteCode="')
SELECT @vSiteCodeEndIndex = CHARINDEX('"',@syncData,@vSiteCodeStartIndex);

SELECT @syncData,SUBSTRING(@syncData, @vSiteCodeStartIndex, @vSiteCodeEndIndex-@vSiteCodeStartIndex );

answered Feb 01 '16 at 09:11

Bhavesh Harsora

655
5
14

This works fine, just a small issue, if the tag doesn't exists it will pick the first value – t-clausen.dk Feb 01 '16 at 09:50

score 1 · Answer 2 · answered Feb 01 '16 at 09:34

1

This works, even on dynamic string:

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123" xqwxqxqxqxq  dqw qdqw qw d'

select substring(@syncData, 
    charindex('SiteCode="',@syncData)+len('SiteCode="'), 
    (charindex('"',@syncData,charindex('SiteCode="',@syncData)+len('SiteCode="'))) - (charindex('SiteCode="',@syncData)+len('SiteCode="')))

answered Feb 01 '16 at 09:34

krtek

382
4
10

This works fine, just a small issue, if the tag doesn't exists it will pick the first value – t-clausen.dk Feb 01 '16 at 09:46
@t-clausen.dk yes, you're right, but simple `if charindex('SiteCode="',@syncData) > 0` do the trick – krtek Feb 01 '16 at 11:19
I know it is minor, already upvoted. Just wanted to mention it – t-clausen.dk Feb 01 '16 at 11:30

Rahul Tripathi · Answer 3 · 2016-02-01T09:09:24.107

0

You can try this:

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"'
select RIGHT(@syncData,LEN(@syncData)-(LEN(@syncData) - CHARINDEX('=', REVERSE(@syncData)) + 1))

SQL FIDDLE DEMO

If you want to get rid of double quotes then you can use

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"'
select REPLACE(RIGHT(@syncData,LEN(@syncData)-(LEN(@syncData) - CHARINDEX('=', REVERSE(@syncData)) + 1)),'"', '')

SQL FIDDLE DEMO

or you can use REPLACE(column,'"', '''') to put the value in single quotes.

edited Feb 01 '16 at 09:09

answered Feb 01 '16 at 09:03

Rahul Tripathi

168,305
31
280
331

This solution will not find a specific tag, it will just find the last tag – t-clausen.dk Feb 01 '16 at 09:38

score 0 · Answer 4 · answered Feb 01 '16 at 09:12

0

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"'
select Left(RIGHT(@syncData,LEN(@syncData)-CHARINDEX('SiteCode="',@syncData)-9),5)

answered Feb 01 '16 at 09:12

Mike Clark

1,860
14
21

This syntax will not work for tags that isn't the last tag – t-clausen.dk Feb 01 '16 at 09:39

score 0 · Answer 5 · answered Feb 01 '16 at 09:30

you can create a function Reference

CREATE FUNCTION [dbo].[fnSplitValues]
(
    @IDs nvarchar(max)
)
RETURNS 
@SplitValues TABLE 
(
    val nvarchar(max)
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @xml xml
    SET @xml = N'<root><r>' + replace(@IDs,' ','</r><r>') + '</r></root>'

    INSERT INTO @SplitValues(val)
    SELECT r.value('.','nvarchar(max)')
    FROM @xml.nodes('//root/r') as records(r)

    RETURN
END

Code Implementation

declare @syncData   nvarchar(4000)
set @syncData = 'MyCol1="ABC" MyCol2="DEF" SiteCode="LA123" MyCol3="GHI"'

SELECT SUBSTRING(REPLACE(val,'"',''), CHARINDEX('=', REPLACE(val,'"','')) + 1, LEN(REPLACE(val,'"','')))
FROM [dbo].[fnSplitValues](@syncData)
where val like '%sitecode%'

t-clausen.dk · Accepted Answer · 2016-02-01T10:11:28.443

Notice how this only refers to @search once and @syncData twice and uses a minimum of functions to optimise performance.

To find the substring in a varchar.

DECLARE @search nvarchar(2000)= 'SiteCode'
DECLARE @syncData nvarchar(4000)='MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"'
SET @search += '="'

SELECT SUBSTRING(x.x, 0, CHARINDEX('"', x.x))
FROM
  (SELECT RIGHT(@syncData, 
   NULLIF(CHARINDEX(REVERSE(@search), REVERSE(@syncData)), 0) - 1) x) x

To find the substring in a table:

DECLARE @search nvarchar(2000)= 'MyCol2'
SET @search += '="'

DECLARE @t table(syncData nvarchar(4000))
INSERT @t values
('MyCol1="ABC" MyCol2="DEF" SiteCode="LA123"'),
('MyCol1="ABD" MyCol2="DEG" SiteCode="LA321"')

SELECT SUBSTRING(x.x, 0, CHARINDEX('"', x.x)) 
FROM @t t
CROSS APPLY
  (SELECT RIGHT(syncData, 
   NULLIF(CHARINDEX(REVERSE(@search), REVERSE(syncData)), 0) - 1) x) x

How to extract a specific string in SQL Server?

6 Answers6