Ad

Extracting Text From A String Before The First Occurrence Of A Symbol

I have data that looks like below-

type       A_URL                          B_URL             C_URL
A         123.com/123abc?....     123.com/123abc?....       123.com/123abc?....
B         pqr.com/435678?....     pqr.com/435678?....       pqr.com/435678?....
C         lmn.com/12u356?....     lmn.com/12u356?....       lmn.com/12u356?....

I want to create a new column URL and do the following-

if type is A extract everything that comes before the first '?' in A_URL column and store it to the new column url,

if type is B extract everything that comes before the first '?' in B_URL column and store it to the new column url,

if type is C extract everything that comes before the first '?' in C_URL column and store it to the new column url

The result should look like below-

type       A_URL                          B_URL             C_URL                     url
A         123.com/123abc?....     123.com/123abc?....       123.com/123abc?....     123.com/123abc?
B         pqr.com/435678?....     pqr.com/435678?....       pqr.com/435678?....     pqr.com/435678?
C         lmn.com/12u356?....     lmn.com/12u356?....       lmn.com/12u356?....     lmn.com/12u356?

Note: Although A_URL, B_URL and C_URL look the same above they have different information after the first "?" stored in each of them.

Ad

Answer

You can use regexp_extract():

select t.*,
       regexp_extract(case when type = 'A' then a_url
                           when type = 'B' then b_url
                           when type = 'C' then c_url
                      end,
                      '^[^?]+'

from t;

EDIT:

If you want to keep the final ?, then use:

select t.*,
       regexp_extract(case when type = 'A' then a_url
                           when type = 'B' then b_url
                           when type = 'C' then c_url
                      end,
                      '^[^?]+[?]'

from t;
Ad
source: stackoverflow.com
Ad