I have following table in Postres
col1
A02BX
A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists
I would like to fetch a substring which is a combination of alphabets and numbers like 'A02BX'. The desired output is:
col1
A02BX
A02BX | A03AE
I am unable to find any solution to this. Though I tried fetching alphanumeric string using following pattern. But is matching the whole string.
[a-zA-Z0-9]+
The output I am getting is the same as initial table:
A02BX
A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists
You can extract all matches using REGEXP_MATCHES and a regular expression that matches alphanumeric substrings between word boundaries that contain at least one letter and at least one digit:
SELECT REGEXP_MATCHES( 'A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists', '\y(?:[AZ]+\d|\d+[AZ])[A-Z0-9]*\y', 'g')See an SQLFiddle .
Details
\y - a word boundary(?:[AZ]+\d|\d+[AZ]) - either 1+ letters and a digit or 1+ digits and then a letter[A-Z0-9]* - 0 or more letters or digits\y - a word boundary.Another solution:
select * from t;
col1
-----------------------------------------------------------------------------------------------------------------------
A02BX
A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists
(2 rows)
select col1, regexp_matches(col1, '(\w\d\d\w\w)+', 'g') from t;
col1 | regexp_matches
-----------------------------------------------------------------------------------------------------------------------+----------------
A02BX | {A02BX}
A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists | {A02BX}
A02BX Other drugs for peptic ulcer and gastro-oesophageal reflux disease (GORD); A03AE Serotonin receptor antagonists | {A03AE}