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.
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 dash1
Start at position 11
Return the first occurencee
Extract a substring using a subexpression(I don't have Redshift running so couldn't test it, but let us know how it goes!)