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://test@test123.com

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, Jun 09 '16 at 07:45
Possible duplicate of [SQL Server XML Parsing](http://stackoverflow.com/questions/35445071/sql-server-xml-parsing) — J. Chomel, Jun 09 '16 at 07:45

Tim Biegeleisen · Accepted Answer · 2016-06-09T08:04:53.383

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 09 '16 at 08:04

answered Jun 09 '16 at 07:45

Tim Biegeleisen

502,043
27
286
360

Hi, this wont work since the only constant here is the and , strings before and after them varies. also the account number between them varies. – Ellyrose Jun 09 '16 at 07:52
@Ellyrose Yes, this will work, provided that `` and its closing tag are unique substrings. Did you even bother trying my code? – Tim Biegeleisen Jun 09 '16 at 07:53
yes i've tried thank you :)...i wonder why its not working..yes the tags for accountnymber is unique – Ellyrose Jun 09 '16 at 07:54
What output are you getting? – Tim Biegeleisen Jun 09 '16 at 07:55
this one of the result of the query..55555.7778881123]]> you see the end part is included... – Ellyrose Jun 09 '16 at 07:56
Do you have more than one `` tag present? – Tim Biegeleisen Jun 09 '16 at 07:57
@Ellyrose I have updated my answer and it should work now, there was a bug in there. – Tim Biegeleisen Jun 09 '16 at 08:06
oh my... why i didn't think of that? :) – Ellyrose Jun 09 '16 at 08:08
When I wrote the query, I assumed an offset of zero for the opening tag, this is why you got extra garbage at the end. The new `CHARINDEX()` term offsets the capture so you get only the account number. – Tim Biegeleisen Jun 09 '16 at 08:09
its now returning what i need.. thanks so much Tim! :) – Ellyrose Jun 09 '16 at 08:09

SQL Charindex and SUbstring

1 Answers1