Search code examples
amazon-web-servicessubstringamazon-redshift

How to take string between two characters in redshift


I have this column in my redshift table and I need to take the string comes after [ and before -

note_internal

package received by [THP - (YBS)]; notes: (Status Manual by jack)
package received by [THP Head Office TRIP 1 - (HO)]; notes: (Status Manual by jack)

The expected result:

result

THP
THP Head Office Trip 1

I have tried to use substring syntax but it did not work

substring(note_internal,charindex('[',note_internal)+1,(((len(note_internal))-charindex('-', reverse(note_internal)))-charindex('-',note_internal))) as result

How can I get my expected result to extract the string after [ and before '?

Thank you in advance.


Solution

  • You should be able to use REGEXP_SUBSTR function - Amazon Redshift, perhaps like this:

    SELECT regexp_substr(note_internal, '\[(.*)-', 1, 1, 'e')
    

    It is saying:

    • \[ Find the first square bracket
    • (.*) Grab lots of characters and mark it as a group to return
    • - Find a dash
    • 1 Start at position 1
    • 1 Return the first occurence
    • e Extract a substring using a subexpression

    (I don't have Redshift running so couldn't test it, but let us know how it goes!)