Redshift SQL - 从字符串中提取数字

Question

在Amazon Redshift表中，我有一个字符串列，需要从中仅提取数字。目前我使用以下方法：

translate(stringfield, '0123456789'||stringfield, '0123456789')

我尝试使用REPLACE函数，但这并不优雅。

有没有将字符串转换为ASCII码，然后进行一些操作以提取数字的想法？或者还有其他选择。 由于Redshift不支持函数并且缺少许多传统函数，所以这很困难。

编辑： 我正在尝试下面的方法，但它只返回051-a92，而我需要的是05192作为输出。我正在考虑使用子字符串等方法，但现在我只有regexp_substr可用。如何摆脱中间的任何字符。

select REGEXP_SUBSTR('somestring-051-a92', '[0-9]+..[0-9]+', 1)

- androboy

---

最好在将其放入Redshift之前解析它-更多的功能，更好的性能，更低的成本... - Guy

@Guy "更多功能，更好的性能，更低的成本..." 哎呀？你有没有研究过大多数分析数据库（Teradata、Vertica等）的成本？Redshift非常便宜，而且SSD节点速度也很_快_。 - Joe Harris

@JoeHarris，我同意Redshift的成本非常低，但这并不意味着你应该用长文本列填充它。如果你可以将它们解析成更分析化的形式（数字、类别、日期...），那么它会更具可扩展性和成本效益。 - Guy

Answer 1

更好的方法是删除所有非数字值：

select REGEXP_replace('somestring-051-a92', '[^0-9]', '')

- 1__

---

如果你想要的是与此相反的效果，即从字符串中删除所有数字，你可以使用以下方法：REGEXP_REPLACE(你的列或字符串, '[0-9/-]', '') - undefined

如果你想要一个浮点数返回，你会怎么做呢？比如98.76。 - undefined

使用REGEXP_replace函数选择'somestring-98.76'，并用正则表达式'[^0-9.]'替换为空字符串。 - undefined

Answer 2

可能有些晚，但我解决了相同的问题，最终得出了这个结果

select REGEXP_replace('somestring-051-a92', '[a-z/-]', '')

或者，你现在可以创建一个Python UDF

- AlexYes

---

REGEXP_replace(value, '[A-Z/-]|[a-z/-]') - Eric Bellet

无法处理特殊字符。 - 1__

Answer 3

通常，您的输入将符合某种模式，可以使用 SUBSTRING() 和 CHARINDEX() { aka STRPOS(), POSITION() } 进行解析。

例如，查找第一个连字符和第二个连字符，并获取它们之间的数据。

如果不是这样（并且假设您的字符范围仅限于 ASCII），那么最好的选择是嵌套 26+ 个 REPLACE() 函数以删除所有标准字母字符（以及任何标点符号）。

但是，如果您的数据中有多字节字符，则无法使用此方法。

- Joe Harris

---

如果我有多字节字符怎么办？ - androboy

哈，这是一个无法启动的项目！如果你真的有多字节字符，那么它们很可能应该在上游某个地方被分割。 - Joe Harris

Answer 4

您可以指定“任何非数字”包括不可打印字符、符号、字母等。

例如，regexp_replace('brws--A*1','[\D]')

返回 "1"

- Matt D