Splitting variable content in SQL-CodePudding

I have a variable in a stored procedure that contains a string of characters like

[Tag]MESSAGE[/Tag]

I need a way to get the MESSAGE part from within the tags. Any help would be much appreciated

CodePudding user response：

What you need is this:

SELECT SUBSTRING(ColumnName,CHARINDEX('html_tag',ColumnName) LEN('html_tag'),CHARINDEX('html_close_tag',ColumnName)-LEN('html_close_tag')) FROM TableName

You'll require to change the html_tag and html_close_tag with your own HTML tag that you want to get rid of.

CodePudding user response：

Note: I have tested it on Oracle RDBMS

A more reliable approach is to use REGEXP_REPLACE.

REGEXP_REPLACE(value, pattern)

Example

SELECT  REGEXP_REPLACE(
        '<Tag>Message</Tag>',
         '\s*</?\w ((\s \w (\s*=\s*(".*?"|''.*?''|[^''">\s] ))?) \s*|\s*)/?>\s*') FROM DUAL;

Just replace "<" with "[" if your tags are different

CodePudding user response：

If the column contains only single tag, simple call of substring function should be enough. Otherwise there will always be some point where regular expression does not suffice since you fall into trap (see this legendary StackOverflow answer).