How to query for a pattern in a DB table with HTML content in the particular column ?

Question

We have a table called with a column which is purely HTML. I need to do a find a pattern like [4digit max]_[6digit number]. Is that possible?

What do you mean by the 'max' in '4digit max'? You mention 'DB' in your question title and 'database' in the tags, but are you trying to do the regex using the database syntax? In that case, what sort of database is it, mySQL? We need more information! — mathematical.coffee
– mathematical.coffee, Commented Dec 21, 2011 at 13:09

mathematical.coffee · Accepted Answer · 2011-12-23 13:18:04Z

1

For Microsoft SQL Server, see here:

select * from Notif_Template
where dbo.regexMatch( Notif_body, '([^0-9]|^)[0-9]{1,4}_[0-9]{6}([^0-9]|$)' ) = 1;

dbo.regexMatch returns if there's a match and 0 otherwise.

answered Dec 23, 2011 at 13:18

57.1k15 gold badges160 silver badges197 bronze badges

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

Comments

Toto · Accepted Answer · 2011-12-21 14:08:07Z

1

As you didn't say wich database, here is a way to do it with PostgreSQL:

SELECT SUBSTRING('abc1234_123456xyz', '(?:[^0-9]|^)([0-9]{1,4}_[0-9]{6})(?:[^0-9]|$)');

  substring
-------------
 1234_123456
(1 row)

answered Dec 21, 2011 at 13:50

Toto

91.7k63 gold badges97 silver badges135 bronze badges