Splitting variable content in SQL

Question

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

What is your database, `CHARINDEX` would work for SQL Server but not for all databases — demircioglu, Jan 20 '22 at 15:34

Yousha Arif · Answer 1 · 2022-01-20T17:51:32.913

1

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

edited Jan 20 '22 at 17:51

answered Jan 20 '22 at 17:13

Yousha Arif

1,188
8
20

Says Regexp is no a built in function name for some reason – Eagz Jan 21 '22 at 06:49
which RDMS are you using? is it MySQL ? – Yousha Arif Jan 21 '22 at 07:19

score 0 · Answer 2 · answered Jan 20 '22 at 15:28

0

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.

answered Jan 20 '22 at 15:28

Vishal A.

1,373
8
19

Product specific answer to a question with no dbms specified. At least tell us which dbms this is for. – jarlh Jan 20 '22 at 16:05
It is for SQL server – Vishal A. Jan 20 '22 at 17:07
It literally says it in the title – Eagz Jan 21 '22 at 06:53

score 0 · Answer 3 · answered Jan 20 '22 at 19:04

0

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).

answered Jan 20 '22 at 19:04

Tomáš Záluský

10,735
2
36
64

Splitting variable content in SQL

3 Answers3