Regex substring with '|' in pattern

Question

I have a string of name|value pairs:

'clientName|img_0196.jpg||mime|||size|3195562||serverFileName|uplo/ad/548a5b35003bf.img_0196.jpg'

I'm trying to extract the value of the clientName, i.e. img_0196.jpg, with:

clientName := substring(eml from '%clientName.#"[^|]+#"%' for '#');

I've tried lots of patterns (different escaping of '|'), but they all yield either NULL or 'clientName'.
Help greatly appreciated.

Curious to know: what was wrong with taking the array's second element, or the second row of the table's list (with regexp_split_to_table()). — Denis de Bernardy
– Denis de Bernardy, Commented Dec 13, 2014 at 19:02
Nothing wrong with that, but I want to know what I am doing wrong here. — LenB
– LenB, Commented Dec 13, 2014 at 21:07
What's wrong is you're mixing LIKE matching syntax and regexp matching syntax. See postgresql.org/docs/current/static/functions-matching.html for details, but in short the percentage at the beginning and end of your expression shouldn't be there, and the # characters look weird too -- not too sure what you're hoping to do with those. — Denis de Bernardy
– Denis de Bernardy, Commented Dec 13, 2014 at 21:31

smathy · Accepted Answer · 2014-12-13 18:47:05Z

0

From my reading of the docs, what you have should work although it doesn't for me either (in 9.3).

I've had many problems with the for version of substring, I just don't trust the whole mixing of % and _ with the regex engine and the weird #" parsing.

Not sure if you're aware, but you can drop the for and get pure POSIX regex, subtring will return the whole match OR the first capturing paren if there is one. So to get what you want:

substring( eml from 'clientName\|([^|]*)')

answered Dec 13, 2014 at 18:47

smathy

28k5 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LenB Over a year ago

Strangely, substring(eml from '%/#"[A-z0-9]+#"[^A-z0-9][^/]+' for '#'); works to get the hex code following the '/'. Maybe it just doesn't like '|' as a character.

smathy Over a year ago

Right, doesn't change my answer. "Works sometimes" is just another way of saying that something is broken :)

Collectives™ on Stack Overflow

Regex substring with '|' in pattern

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related