0

This is for Oracle, not MySql in case there might be a slight difference.

I am trying to get paths of this url

select regexp_substr(
'https://domain/l202108091338.csv.zip?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAQ6R%2F20210820%2Fca-central-1%2Fs3%2Faws4_request&X-Amz-Date=20210820T223109Z&X-Amz-Expires=300&X-Amz-Signature=bd3d40250afdc751c67c58302a2ca152c7&X-Amz-SignedHeaders=host',
'[^=]+',
1,
2
) from dual;

the above command outputs

AWS4-HMAC-SHA256-Amz-Credential

but the actually value is

AWS4-HMAC-SHA256&X-Amz-Credential

How can I make the output includes &X too?

Thanks in advance for any suggestions and help.

Dora
  • 6,776
  • 14
  • 51
  • 99
  • Cannot replicate the problem [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=785bc13f6792004b1ce8a92c2dd3d232). – MT0 Aug 20 '21 at 23:06
  • @MT0 weird, if you run it in Oracle SQL Developer or in ODI, it will be empty :( – Dora Aug 20 '21 at 23:18
  • 2
    Possibly a duplicate of https://stackoverflow.com/q/2333994/1509264 or https://stackoverflow.com/q/34332639/1509264 If using `SET DEFINE OFF` fixes the issue then it is going to be a duplicate. – MT0 Aug 20 '21 at 23:19
  • @MT0 but I wonder would this affect the whole db and not a good practice to do? – Dora Aug 20 '21 at 23:34
  • 1
    `set define off` turns the `define` setting off for the current `SQL*Plus` session. It has no impact on other sessions or the database as a whole. It's a client-only setting. – Justin Cave Aug 20 '21 at 23:38
  • No, SQL/Plus and SQL Developer both would be treating `&X` as a substitution variable and substituting it with a replacement on the **client** side so that the server never sees the `&X` and only sees the replacement (which is presumably an empty string). Using `SET DEFINE OFF` will only affect your client and not the database. – MT0 Aug 20 '21 at 23:38
  • @JustinCave and MT0 Thanks a lot for clarifying ^_^ – Dora Aug 20 '21 at 23:41

0 Answers0