英文:
REGEXP_SUBSTR in Snowflake, regex that will extract the string after the period (which occurs between 0-2 times)
问题
我正在尝试在Snowflake中使用REGEXP_SUBSTR方法来提取句点后的字符串。我有以下可能性：
ALTER TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME ADD COLUMN ....ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD COLUMN ....ALTER TABLE TABLE_NAME ADD COLUMN ....
在这些可能性中，我需要能够提取TABLE_NAME。到目前为止，我尝试过以下方法，可以获取第三种可能性：
select regexp_substr(query_text, 'ALTER TABLE ([0-9A-Za-z\\-\\_]+) ADD COLUMN', 1, 1, 'e', 1);
我不确定如何在一个正则表达式中捕获所有三种可能性。感谢任何指点。谢谢。
英文:
I'm trying to use the REGEXP_SUBSTR method in Snowflake to extract a string after the period. I have the following possibilities:
ALTER TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME ADD COLUMN ....ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD COLUMN ....ALTER TABLE TABLE_NAME ADD COLUMN ....
With the following possibilities, I need to be able to extract TABLE_NAME. So far, I've tried this which is able to get the third possibility:
select regexp_substr(query_text, 'ALTER TABLE ([0-9A-Za-z\\-\\_]+) ADD COLUMN', 1,1, 'e', 1);
I'm not sure how I could capture all three possibilities in one regular expression. Would appreciate any pointers. Thank you.
答案1
得分: 1
使用正则表达式和split_part：
WITH cte(query_text) AS (
SELECT 'ALTER TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME ADD COLUMN ....' UNION
SELECT 'ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD COLUMN ....' UNION
SELECT 'ALTER TABLE TABLE_NAME ADD COLUMN ....'
)
SELECT
regexp_substr(query_text, 'ALTER TABLE (.+) ADD COLUMN', 1,1, 'e', 1) as obj_name,
split_part(obj_name, '.', -1) AS tab_name,
split_part(obj_name, '.', -2) AS schema_name,
split_part(obj_name, '.', -3) AS db_name
FROM cte;
输出：
| OBJ_NAME | TAB_NAME | SCHEMA_NAME | DB_NAME |
|---|---|---|---|
| DATABASE_NAME.SCHEMA_NAME.TABLE_NAME | TABLE_NAME | SCHEMA_NAME | DATABASE_NAME |
| SCHEMA_NAME.TABLE_NAME | TABLE_NAME | SCHEMA_NAME | |
| TABLE_NAME | TABLE_NAME |
英文:
Using regexp and split_part:
WITH cte(query_text) AS (
SELECT 'ALTER TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME ADD COLUMN ....' UNION
SELECT 'ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD COLUMN ....' UNION
SELECT 'ALTER TABLE TABLE_NAME ADD COLUMN ....'
)
select
regexp_substr(query_text, 'ALTER TABLE (.+) ADD COLUMN', 1,1, 'e', 1) as obj_name,
split_part(obj_name, '.', -1) AS tab_name,
split_part(obj_name, '.', -2) AS schema_name,
split_part(obj_name, '.', -3) AS db_name
FROM cte;
Output:
| OBJ_NAME | TAB_NAME | SCHEMA_NAME | DB_NAME |
|---|---|---|---|
| DATABASE_NAME.SCHEMA_NAME.TABLE_NAME | TABLE_NAME | SCHEMA_NAME | DATABASE_NAME |
| SCHEMA_NAME.TABLE_NAME | TABLE_NAME | SCHEMA_NAME | |
| TABLE_NAME | TABLE_NAME |
答案2
得分: 0
select regexp_substr(query_text, 'ALTER TABLE ([0-9A-Za-z\\-\\_.]+\\.)?([0-9A-Za-z\\-\\_]+) ADD COLUMN', 1, 1, 'e', 2);
select regexp_substr(query_text, 'ALTER TABLE ([\"0-9A-Za-z\\-\\_.]+\\.)?\"?([0-9A-Za-z\\-\\_]+)\"? ADD COLUMN', 1, 1, 'e', 2);
英文:
select regexp_substr(query_text, 'ALTER TABLE ([0-9A-Za-z\\-\\_.]+\.)?([0-9A-Za-z\\-\\_]+) ADD COLUMN', 1, 1, 'e', 2);
Will do what you described.
select regexp_substr(query_text, 'ALTER TABLE (["0-9A-Za-z\\-\\_.]+\.)?"?([0-9A-Za-z\\-\\_]+)"? ADD COLUMN', 1, 1, 'e', 2);
Will also extract table name if it is in quotes.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论