When using snowflake I need everything except the last part of these:

for example1 :

xxx | YYY | XXX | asda | dasd12 | adasda

I just need

xxx | YYY | XXX | asda | dasd12

example2:

32131| Y\ZYY | XXX | asda | dasd12 | 213131 | adsadfd

I just need

32131| Y\ZYY | XXX | asda | dasd12 | 213131

can anyone help me please? The number of pipes can be random I need everything except the last pipe and whatever there is after

CodePudding user response：

there may be prettier ways of doing this but this should work (the functions to use are in caps)

1. REVERSE the string
2. Find the POSITION of the first pipe
3. Get the RIGHT portion of the string from that pipe position
4. REVERSE the result

CodePudding user response：

You can split the string to array, slice it, and stitch it back as string

 select array_to_string(array_slice(split('aa|bb|cc|dd','|'),0,-1),'|')

In case you fancy regex

select regexp_substr('aa|bb|cc|dd','(.*)[|]',1, 1, 'e')