提取某个标点符号正则表达式前的字符串

Question

如何在presto SQL中提取第一个标点符号|之前的单词?

表

+----+------------------------------------+
| id |              title                 |
+----+------------------------------------+
|  1 | LLA | Rec | po#069762 | saddasd    |
|  2 | Hello amustromg dsfood             |
|  3 | Hel | sdfke bones.                 |
+----+------------------------------------+

输出

+----+------------------------------------+
| id |              result                |
+----+------------------------------------+
|  1 | LLA                                |
|  2 |                                    |
|  3 | Hel                                |
+----+------------------------------------+

尝试

REGEXP_EXTRACT(title, '(.*)([^|]*)', 1)

谢谢

Answer 1

使用基本字符串函数，我们可以尝试:

SELECT id,
CASE WHEN title LIKE '%|%'
THEN TRIM(SUBSTR(title, 1, STRPOS(title, '|') - 1))
ELSE '' END AS result
FROM yourTable
ORDER BY id;