How to extract a substring from a string in SQL?-CodePudding

The environment I am currently working in is Snowflake. As a matter of data sensitivty, I will be using pseudonyms for my following question.

I have a specific field in one of my tables called FIELD_1. The data in this field is structured as such:

The idea is to use a query to pull FIELD_2 from FIELD_1.

I have been trying out this query:

SELECT FIELD_1, SUBSTRING(PROMOTION_NAME, 1,13) AS FIELD_2 FROM TABLE 1;

This query would work if all my strings in FIELD_1 had the same number of characters. That is not the case. Ideally, I would need to be able to extract from FIELD_1 all characters up until the first 4 numbers, without the underscores. Anybody have any idea what kind fo query would get me this output?

Thanks!

CodePudding user response：

maybe using the SPLIT_PART() that snowflake provides. Take a look at https://docs.snowflake.com/en/sql-reference/functions/split_part.html

CodePudding user response：

If you want a pure SQL way to do it, this will work. It looks for a hyphen in the final string instead of assuming it will always be in the third position. You can also use a JavaScript UDF to do this, and for this particular use it may be more performant. In any case, here's a SQL approach:

create or replace table T1(S string);
insert into T1 (S) values ('Blue_Car_20210923-750ff'), ('Red_Car_20210924-60off');

select listagg(iff(VALUE like '%-%', left(VALUE, 4), VALUE), ' ') as STR
from T1, table(split_to_table(S, '_')) CARS group by SEQ;