CODEWITHSUNDEEP
sqlsql-server-2008
Ellyrose
Ellyrose

Reputation: 23

SQL Charindex and SUbstring

I'm trying to figure out how to extract the AccountNumber on one of my columns with below sample value:

><AccountNumber>12345678</AccountNumber><Links>http://[email protected]

the string length before and after the account number varies.

I have tried below code but the i cant figure out how to just extract the AccountNumber. Even the account number has different lengths.

Select  substring(XmlData,
                   charindex('><AccountNuber',XMLData),
                 50 )
   from Item with(nolock)

Upvotes: 2

Views: 567

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

The following solution should work if you only have a single <AccountNumber> tag for each record of the XmlData column.

SELECT SUBSTRING(XmlData,
                 CHARINDEX('<AccountNumber>', XmlData) + 15,
                 CHARINDEX('</AccountNumber>', XmlData) -
                     (CHARINDEX('<AccountNumber>', XmlData) + 15));

If you want to extract multiple values, or if a given record could have multiple tags, then this approach won't work and you should consider using regular expressions, or better yet, an XML parser.

Upvotes: 1

Related Questions