Patrick Gregorio Patrick Gregorio - 3 months ago 24
SQL Question

Oracle SQL regex remove text in square brackets at end of field

I have a field in an Oracle database where it is formatted in a certain way like so:

Some text with <sup>superscripts</sup> [stock #random-number] [dynamic text]


I need to remove the texts
[stock #random-number]
and
[dynamic text]
so that I'm left with
Some text with <sup>superscripts</sup>
and then trim the result so no leading and trailing spaces.

I need to do this inside the query and not on the PHP side as I need to use
DISTINCT
.

Everything is dynamic so what I want doesn't necessarily end in
<sup>
.

Answer

You could use the trim, substr, instr

 SELECT  trim(substr(your_string, 1, INSTR( your_string,'['))) 
 FROM your_table