I want to extract,
002267b4-ad06-11e4-89ca-59f94b49bbc0
the above string from another string. What I have tried is,
select regexp_matches('class 1 type 1 cat 1 002267b4-ad06-11e4-89ca-59f94b49bbc0' , '\b\w{1,8}\-\w{1,4}\-\w{1,4}\-\w{1,4}\-\w{1,12}\b' ) This does not give any result, BUT when I check the regular expression from an online tool it selects the correct string I want. Must be an issue in the postgresql query.
Please help.
Accoding to PostgreSQL regex documentation, you need to use \m for a beginning of a word, and \M for the end of the word boundaries. \b means a backspace. So, your regex should lool like:
select regexp_matches('class 1 type 1 cat 1 002267b4-ad06-11e4-89ca-59f94b49bbc0' , '\m\w{1,8}\-\w{1,4}\-\w{1,4}\-\w{1,4}\-\w{1,12}\M' ) Try this query:
SELECT regexp_matches( 'class 1 type 1 cat 1 002267b4-ad06-11e4-89ca-59f94b49bbc0', '[^-]{1,8}-[^-]{1,4}-[^-]{1,4}-[^-]{1,4}-[^-]{1,12}' ); This can be Fiddel around with
()\bis a shortcut for "backspace" - I don't think this is what you want. I also don't think you need to escape the-outside of a range operator ([..]). I think'\w{1,8}-\w{1,4}-\w{1,4}-\w{1,4}-\w{1,12}'should do it[[:<:]]and[[:>:]]instead of first and last\b. See postgresql.org/docs/8.3/interactive/….\mfor the first\band\Mfor the last\b:\m\w{1,8}\-\w{1,4}\-\w{1,4}\-\w{1,4}\-\w{1,12}\M.\bmeans a backspace in postgresql regex flavor.