英文:
REGEXP_SUBSTR usage
问题
我有一个Snowflake表，其中包含值为[6326.72, -548.99, -80.29]的列。我需要使用SELECT语句创建三个新列，col1的值为6326.72，col2的值为-548.99，col3的值为-80.29。
我尝试使用正则表达式-?\d+\.\d+来实现这一点，但出于某种原因，它没有返回正确的结果。有人可以就如何使用Snowflake REGEXP_SUBSTR来满足这个要求提供建议吗？
SELECT REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.\d+', 1, 1) AS col1,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.\d+', 1, 2) AS col2,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.\d+', 1, 3) AS col3;
谢谢！
英文:
I have a Snowflake table with a column containing the values [6326.72, -548.99, -80.29]. I need to create three new columns, col1 with a value of 6326.72, col2 with a value of -548.99, and col3 with a value of -80.29 using a SELECT statement.
I tried using the regular expression -?\d+\.+\d+ to achieve this, but for some reason, it is not returning the correct result. Can anyone advise on this requirement of using a regular expression with Snowflake REGEXP_SUBSTR?
SELECT REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.+\d+', 1, 1) AS col1,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.+\d+', 1, 2) AS col2,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '-?\d+\.+\d+', 1, 3) AS col3;
Thank you!
答案1
得分: 1
我不认为你需要使用正则表达式来完成这个任务。以下是可以得到你期望结果的查询：
SELECT split( trim( '[6326.72, -548.99, -80.29]' , '[]' ) ,',') x,
x[0]::varchar col1,
x[1]::varchar col2,
x[2]::varchar col3;
+--------------------------------------------+---------+----------+---------+
| X | COL1 | COL2 | COL3 |
+--------------------------------------------+---------+----------+---------+
| [ "6326.72", " -548.99", " -80.29" ] | 6326.72 | -548.99 | -80.29 |
+--------------------------------------------+---------+----------+---------+
SPLIT: https://docs.snowflake.com/en/sql-reference/functions/split
TRIM: https://docs.snowflake.com/en/sql-reference/functions/trim
不过，这里也提供了一个使用正则表达式的版本：
SELECT REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 1) AS col1,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 2) AS col2,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 3) AS col3;
请注意，这两种方法都可以实现相同的结果。
英文:
I don't think you need REGECP for this. This should give the result you expected:
SELECT split( trim( '[6326.72, -548.99, -80.29]' , '[]' ) ,',') x,
x[0]::varchar col1,
x[1]::varchar col2,
x[2]::varchar col3;
+--------------------------------------------+---------+----------+---------+
| X | COL1 | COL2 | COL3 |
+--------------------------------------------+---------+----------+---------+
| [ "6326.72", " -548.99", " -80.29" ] | 6326.72 | -548.99 | -80.29 |
+--------------------------------------------+---------+----------+---------+
SPLIT: https://docs.snowflake.com/en/sql-reference/functions/split
TRIM: https://docs.snowflake.com/en/sql-reference/functions/trim
Anyway, here is the REGEXP version:
SELECT REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 1) AS col1,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 2) AS col2,
REGEXP_SUBSTR('[6326.72, -548.99, -80.29]', '([^\\[\\], ]+)', 1, 3) AS col3;
答案2
得分: 1
使用regexp_substr_all的变化
select '[3456.7288, -23425.9999, -234234.29]' as num_string,
regexp_substr_all(num_string,'[0-9.-]+') as num_array,
num_array[0]::varchar as num1, --根据需要转换为适当的数据类型
num_array[1]::varchar as num2,
num_array[2]::varchar as num3
英文:
A variation using regexp_substr_all
select '[3456.7288, -23425.9999, -234234.29]' as num_string,
regexp_substr_all(num_string,'[0-9.-]+') as num_array,
num_array[0]::varchar as num1, --cast to appropriate data type as desired
num_array[1]::varchar as num2,
num_array[2]::varchar as num3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论