Question Re: recognize and exclude sql comments in SELECT LIKE statement in Technology Q&A

recognize and exclude sql comments in SELECT LIKE statement

Baron — Wed, 19 Apr 2023 03:23:01 GMT

Is it possible to look for a specific text and distinguish whether the matched text exists in a comment?

Something like this:

select 'really found' where 'select * from ABC;' like '%ABC%';
select 'found only in comments' where 'select * from mytable;--other table than ABC' like '%ABC%';

this works, but I want to prevent this working:

select 'really found' where 'select * from mytable;--other table than ABC' like '%ABC%';
select 'found only in comments' where 'select * from ABC;' like '%ABC%';

Re: recognize and exclude sql comments in SELECT LIKE statement

MCMartin — Wed, 19 Apr 2023 09:51:55 GMT

have you checked the REGEXP search condition to solve the match inside a comment? Eventually using --.*?ABC.*?

Re: recognize and exclude sql comments in SELECT LIKE statement

VolkerBarth — Wed, 19 Apr 2023 10:13:27 GMT

I guess it should also be considered that a new line ends a "--comment" or "// comment" so text in the next line up to the next comment indicator must be recognized as non-comment - which should work with according REGEXP expressions when adding '\\n', too.