Postgresql – How to pick a chunk from the text column in postgresql

postgresql-9.6

I have a table ABC with the following 2 columns

id(PK)        Name

1             Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) 56 tablet 4 x 14 tablets
2             Zocor 10mg tablets (You & Me Ltd) 28 tablet 2 x 14 tablets
3             Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) 20 tablet 2 x 10 tablets

I would like to select 56 tablet from id=1, 28 tablet from id=2 and 20 tablet from id=3. Is there anyway in Postgres to select just the chunk from the text?

Best Answer

create table abc (id int primary key, name text);
insert into abc values 
(1, 'Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) **56 tablet** 4 x 14 tablets'),
(2, 'Zocor 10mg tablets (You & Me Ltd) **28 tablet** 2 x 14 tablets'),
(3, 'Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) **20 tablet** 2 x 10 tablets');

If there is a pattern, for example: (1 or more digits) plus the word tablet, you can use a regular expression like this:

select 
    substring(name, '([0-9]+ tablet)')
from
    abc;

| substring |
| :-------- |
| 56 tablet |
| 28 tablet |
| 20 tablet |

db<>fiddle here

Related Question