CODEWITHSUNDEEP
sqlregexamazon-redshift
Garfield
Garfield

Reputation: 143

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

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!

Upvotes: 0

Views: 262

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12694

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#

Upvotes: 0

trillion
trillion

Reputation: 1401

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

Demo for the regex output

Upvotes: 2

Related Questions