How can I automatically extract content from a field in a SQL query?-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:

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!

CodePudding user response：

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}'), '_', ' ')

CodePudding user response：

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