Extract data from sql string

Question

I have the following data in a column. I want to extract the 'matching details' score just showing as 542. The problem is the matching score can also be more than 3 characters long. Can someone help?

 MatchingDetails score="542" maxScore="-96" matchRule="abcdef"><rule name="Person_Forename" score="279" /><rule name="Person_Surname" score="263"

Zohar Peled · Accepted Answer · 2018-05-01 11:14:07Z

1

One way is to use a combination of charindex, patindex, and substring:

DECLARE @S varchar(100) = 'MatchingDetails score="542" maxScore="-96" matchRule="abcdef">'


SELECT SUBSTRING(@S, 
                 patindex('% score="%', @S) + 8,
                 charindex('"', @S, patindex('% score="%', @S) + 9) - patindex('% score="%', @S) - 8)

Result:

answered May 1, 2018 at 11:14

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JSY Over a year ago

Thank you so much, that's so helpful!

John Cappelletti · Accepted Answer · 2018-05-01 12:07:44Z

1

If your data is an XML string, perhaps something like this

Example (corrected xml)

Declare @S varchar(max) = '
<MatchingDetails score="542" maxScore="-96" matchRule="abcdef" >
    <rule name="Person_Forename" score="279"></rule>
    <rule name="Person_Surname" score="263"></rule>
</MatchingDetails>
'

Select convert(xml,@S).value('MatchingDetails[1]/@score','int')

Returns

answered May 1, 2018 at 12:07

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

Extract data from sql string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related