how to remove characters after digits in snowflake

Question

I have a string like this '22-ABCD-BC-220602-11484-TEST' and I want to remove the word 'TEST' from end of this string. The point is 'TEST' changes each time and is not a constant variable. so what I want to do is writes query to remove what ever comes after digits at the end of string. like in this case I want it to remove whatever comes after 220602-11484.

I used this query

select REGEXP_REPLACE('22-ABCD-BC-220602-11484-TEST','([0-9]{6}-[0-9]{5}).*$','')

to replace the end part with space, but the result I get is '22-ABCD-BC-' and it removes the digits as well

Answer 1

select left('22-ABCD-BC-220602-11484-TEST', len('22-ABCD-BC-220602-11484-TEST') - charindex('-', reverse('22-ABCD-BC-220602-11484-TEST')));

We are basically taking in account everything left before that last hyphen.

Reference DOC:

• https://docs.snowflake.com/en/sql-reference/functions/left.html https://docs.snowflake.com/en/sql-reference/functions/reverse.html https://docs.snowflake.com/en/sql-reference/functions/charindex.html