SQL Server: regex search capture group count

Question

I'm currently working on a search method in C# for a SQL Server database.

The regex:

/(a)|(b)|(c)|(d)/g

a, b, c & d are the search keywords.

The string that I apply the regex to:

a fdh eidb

Consists of random words(represented as letters) and some of the keywords from above.

Expected output:

3 keywords matches.

But how does a SQL query for SQL Server look like that returns a table with a Matches column with the keyword match count for each row?

I know how to do this in C# but I want to do it in the search query itself so I can sort the output.

Thanks for any help towards the right direction :)

[CLR Functions](https://msdn.microsoft.com/en-us/library/ms189876.aspx) could be useful. — Andrew Morton, Oct 29 '16 at 16:12

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

I don't think there is a way to do regular expressions in SQL Server queries - other than adding some managed code which adds that functionality.

Here is an example of how to do that - SQL Server Regular expressions in T-SQL

edited May 23 '17 at 12:16

Community

1
1

answered Oct 29 '16 at 17:26

reddal

353
2
8

Thanks, too bad that it's not possible without adding code like in MYSQL. I wrote a sql cursor function as solution for now ^^ – seahorsepip Oct 30 '16 at 09:56

score 0 · Accepted Answer · answered Oct 30 '16 at 09:53

It seems that REGEX wasn't really the solution.

Instead I wrote multiple SQL functions that do the job:

CREATE FUNCTION [dbo].[KeywordMatches]
(
    @String nvarchar(1000),
    @Keywords nvarchar(1000),
    @Seperator text
)
RETURNS INT
AS
BEGIN
    DECLARE @Count int = 0;
    DECLARE @Keyword varchar(1000);
    DECLARE KeywordsCursor CURSOR FOR  
    SELECT *
    FROM [dbo].StringSplit(@Keywords, @Seperator)

    OPEN KeywordsCursor   
    FETCH NEXT FROM KeywordsCursor INTO @Keyword   

    WHILE @@FETCH_STATUS = 0   
    BEGIN
        IF @String LIKE '%' + @Keyword + '%'
            SET @Count += 1
        FETCH NEXT FROM KeywordsCursor INTO @Keyword
    END

    CLOSE KeywordsCursor   
    DEALLOCATE KeywordsCursor

    RETURN @Count
END

And (fallback for server 2016 split_string):

CREATE FUNCTION [dbo].[StringSplit]
(
    @SeperatedWords nvarchar(1000),
    @Seperator char
)
RETURNS @Words TABLE
(
    Word nvarchar(1000)
)
AS
BEGIN
    DECLARE @Position int = -1
    SET @SeperatedWords += @Seperator
    WHILE (@Position > 0 OR @Position = -1)
    BEGIN
        SET @SeperatedWords = SUBSTRING(@SeperatedWords, @Position + 1, LEN(@SeperatedWords) - @Position + 1)
        SET @Position = CHARINDEX(@Seperator, @SeperatedWords)

        /* Only add words that have a length bigger then 0 */
        IF @Position > 1
            /* Add the word to the table */
            INSERT INTO @Words(Word) VALUES(LEFT(@SeperatedWords, @Position - 1))
    END
    RETURN
END

Usage:

SELECT Id, Title, [dbo].KeywordMatches(Title, 'blue red green', ' ') AS Matches
FROM Questions
ORDER BY Matches DESC, Date DESC

Above query orders by the amount of keywords found in the title and date.

I also read about full text search which is probably faster then this solution.

SQL Server: regex search capture group count

2 Answers2