How can I automatically extract content from a field in a SQL query?

Question

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:

I am trying to figure out how to automatically extract from my FIELD_1 the output I have in FIELD_2.

Does anyone have any idea what kind of query I would need to achieve this? Any help would be GREATLYappreciated! I am really quite stuck on this problem.

Thank you!

user_1144035 · Answer

You seem to want everything up to the first four numbers. Then to replace the underscores with spaces. If so:

select replace(regexp_substr(field_1, '^[^0-9]*[0-9]{4}'), '_', ' ')

Or alternatively, if you want the first three components separated by underscores:

select replace(regexp_substr(field_1, '^[^_]+_[^_]+_[0-9]{4}'), '_', ' ')

user_269970 · Answer

If the data is as simplistic in reality as you've described here, you can use a variable-length LEFT() function in conjunction with REPLACE() to get the desired output:

SELECT FIELD_1, REPLACE(LEFT(FIELD_1, LEN(FIELD_1)-10),'_',' ') AS FIELD_2
FROM table_name

How can I automatically extract content from a field in a SQL query?

2 Answers