Match substring with alphanumeric characters (ignoring other substrings) in postgres 11

user_12436050 0
Viewed 256

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
postgresql regex
2 Answers

You may extract all matches using REGEXP_MATCHES and a regex that matches alphanumeric substrings in 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(?:[A-Z]+\d|\d+[A-Z])[A-Z0-9]*\y',
       'g')

See an SQLFiddle.

Details

  • \y - a word boundary
  • (?:[A-Z]+\d|\d+[A-Z]) - 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.
user_38329700

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}
user_107268500
Related