REGEXP_LIKE conversion in SQL Server T-SQL

Question

I have come across this line in an old report that needs converting to SQL Server.

REGEXP_LIKE (examCodes, learner_code)

examCodes being the source and learner_code being the pattern. I know that SQL Server doesn't have REGEXP_LIKE and most places tell you to use PATINDEX.

Here's me thinking that this would work:

PATINDEX(learner_code, examCodes)

But I get the error:

Msg 4145, Level 15, State 1, Line 54
An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'.

On MSDN the syntax is specified as,

PATINDEX ('%pattern%',expression) 

But learner_code is a field and I can't specify a pattern?

I did not write this report in the first place so I'm puzzled to what the pattern it's looking for anyway.

Many thanks

Answer 1

WHERE PATINDEX ('%pattern%',expression)  !=0

If pattern is found , PATINDEX returns non zero value and you need to do a comparison in WHERE clause. A WHERE clause must be followed by comparison operation that returns true / false.

May be you are using PATINDEX without doing the comparison and that is why error message shows non boolean expression near WHERE clause.

To search for pattern learner_code with wildcard character

WHERE PATINDEX ('%' + CAST(learner_code AS VARCHAR) +'%',examCodes)  !=0

Answer 2

ORACLE'S REGEXP_LIKE supports actual regular expressions, PATINDEX only supports the % and _ wildcards, the [] list/range, and the ^ list/range negation.