Extracting value from String in SQL

Question

I have a SQL table with a column containing XML. It's not pretty but it is part of the project and noch changeable.

Now I need to extract a value from a specific element of this XML. Value is a federal state, so the length of the value is variable, what means that

SUBSTRING ( expression ,start , length )

is not an option, as i can't say how long my value will be.

Is there a function that allows to search between two expression, like

FOO ('state>', '</state')

or anything in that direction?

EDIT:

I'm using MySQL 5.6

Example:

<root>
  <person>
    <name>Michael</name>
    <state>Berlin</state>
  </person>
</root>

my output should be

Berlin

where a different person can have a different state and so length of value is not allways the same.

thanks for your time.

Show us some sample table data and the expected result - all as formatted text, not images. — jarlh
– jarlh, Commented Jan 16, 2019 at 8:31
Maybe it's better to do it in Java, .Net, ... any language you want. — Daniel E.
– Daniel E., Commented Jan 16, 2019 at 8:34
Try solution described here stackoverflow.com/questions/30172378/… — barbariania
– barbariania, Commented Jan 16, 2019 at 8:43

AdrianL · Accepted Answer · 2019-01-16 11:45:28Z

0

INSTR (str, pattern)

was the solution

and my script to select the value in my string looks like this

SELECT ID, substring(
xml,
instr(xml, "<state>") + 23,
(instr(xml, "</state>") - (instr(xml, "<state>") + 23))
) AS STATE
FROM tablexml 
WHERE xml
LIKE '%value%';

answered Jan 16, 2019 at 11:45

AdrianL

3453 silver badges22 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-01-16 12:14:46Z

0

You can use substring_index():

select substring_index(substring_index(expression, '<state'>, 2), '</state>', -1) as state
from t;

answered Jan 16, 2019 at 12:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Extracting value from String in SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related