SQLTeam.com | Weblogs | Forums

Regexp_count() in Sql

Hi
what is an alternative to regexp_count() in sql ?

we have no idea what regexp_count is. Could you please provide sample data and the desired final result?

Unfortunately there is no support for regular expressions in native T-SQL. You could use SQL CLR to create a CLR user defined function. You will have the dotnet regex features available to you in the CLR function.

A poor alternative (not for regular expressions, but for simple strings) would be something like below:

DECLARE
@str VARCHAR(32) = '123ABC123XYZ',
@substr VARCHAR(32) = '123';
SELECT (LEN(@str) - LEN(REPLACE(@str,@substr,'')))/NULLIF(LEN(@substr),0);

Not pleasing to the eye, or the mind, is it? :rofl:

1 Like

There is a CLR library, SQL#, which has a free version:

https://sqlsharp.com/

This has a RegEx_Matches function so counting the number of rows returned would be the same as Oracle's regexp_count().

1 Like