SQL Charindex and SUbstring

Question

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)

You may need to have a look to regexp parsing for SQL Server. But only if the is the only purpose of modifying this specific string. Don't user regexp to extensively parse XML or HTML. Use appropriate tool. Maybe SQL Server has XML parsing utility too? this could solve your issue too. — J. Chomel
– J. Chomel, Commented Jun 9, 2016 at 7:45

Tim Biegeleisen · Accepted Answer · 2016-06-09 08:04:53Z

1

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.

edited Jun 9, 2016 at 8:04

answered Jun 9, 2016 at 7:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

10 Comments

Ellyrose Over a year ago

Hi, this wont work since the only constant here is the <AccountNumber> and </AccountNumber>, strings before and after them varies. also the account number between them varies.

Tim Biegeleisen Over a year ago

@Ellyrose Yes, this will work, provided that <AccountNumber> and its closing tag are unique substrings. Did you even bother trying my code?

Ellyrose Over a year ago

yes i've tried thank you :)...i wonder why its not working..yes the tags for accountnymber is unique

Tim Biegeleisen Over a year ago

What output are you getting?

Ellyrose Over a year ago

this one of the result of the query..55555.7778881123</AccountNumber><Link /></Scanned:Exception>]]> you see the end part is included...

|

Collectives™ on Stack Overflow

SQL Charindex and SUbstring

1 Answer 1

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Linked

Related