How to extract a substring from a string in SQL?

user_15268236 0
Viewed 148

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:

enter image description here

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!

snowflake-cloud-data-platform sql
2 Answers

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;

Update: To put into individual columns, you would just do a subselect or CTE and select from positions in the array like this:

with X as
(
select array_agg(iff(VALUE like '%-%', left(VALUE, 4), VALUE)) as STR
from T1, table(split_to_table(S, '_')) CARS group by SEQ
)
select STR[0]::string as COLOR, STR[1]::string as ITEM, STR[2]::int as YEAR from X
;
user_127563810
user_173605630
Related