sql server - SQL How do I get a part of a value that is between between specific left and right value

Question

I would like to know what select statement can be used to get a piece of a column value that is between a specific value on the right and on the left.

Table contains the following information.

shipment_plsid	tag	element
1948	INCOTERMS	INCOTERMS VER="1">TYP>4/TYP>VAR>SHIPPER/VAR>/INCOTERMS>
2023	INCOTERMS	INCOTERMS VER="1">TYP>4/TYP>VAR>DDP/VAR>/INCOTERMS>

question from:https://stackoverflow.com/questions/65909547/sql-how-do-i-get-a-part-of-a-value-that-is-between-between-specific-left-and-rig

Answer 1

If you are using MySQL, this is what you need:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('VER="1">TYP>4/TYP>VAR>SHIPPER</VAR</INCOTERMS', "</VAR", 1), "VAR>", -1);

For example:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('VER="1">TYP>4/TYP>VAR>SHIPPER</VAR</INCOTERMS', "</VAR", 1), "VAR>", -1) as result;
+----------+
| result   |
|----------|
| SHIPPER  |
+----------+
1 row in set
Time: 0.011s

If you are using another db, please let us know which one it is