extract a string before certain punctuation regex

Question

How to extract words before the first punctuation | in presto SQL?

Table

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

Output

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

Attempt

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

Thank you

Answer 1

Using the base string functions we can try:

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