SQL: Regex to extract everything between first and last occurrence of a character-CodePudding

I have a table with a string column like this:

------------------------------------------------
| Column                                       | 
------------------------------------------------
| #Extract this# and #this too# do not extract |
------------------------------------------------
| Leave this and #get this out#                |
------------------------------------------------

I want to extract everything from first # occurrence and the last # occurrence like this:

--------------------------------
| Expected Output              |
--------------------------------
| #Extract this and #this too# |
--------------------------------
| #get this out#               |
--------------------------------

I have tried regexp_substr(column, '#[^.]#', 1, regexp_count(column, '#')) but it is giving me empty string.

Does anyone know how to fix this?

Thanks in advance!

CodePudding user response：

select REGEXP_SUBSTR(column, '#.*#') as pattern from [table]

Demo for the regex output

CodePudding user response：

I will use substring to get the first and last occurrence of #. I added 2 in the length of the string because we want to include #.

SELECT 
  substring(col 
           from 
              position('#' in col)  
           for 
              length(col) - position('#' in reverse(col)) - position('#' in col)   2) 
FROM 
    table;

Result:

substring
#Extract this# and #this too#
#get this out#