Search a string - whole words only

Database
Junior Supporter

Search a string - whole words only

Hi,

I have a requirement to see who is using a "Sign" function in queries. If i give a condition like  b.SqlTextInfo like '%SIGN%'  sqls having string - SIGNON_TIME also get qualified. I want to just check for word "Sign' or "syslib.Sign" and texts like - SIGNON_TIME should not qualify.

Could anyone help me how to do this ? To draw a parallel, the way we can search for "whole word" in MSWord.

--Samir


Accepted Solutions
Junior Contributor

Re: Search a string - whole words only

You can use a Regular Expression, e.g.

WHERE RegExp_Similar(x, '.*\bsign\b.*','i')= 1

As '\b' indicates a word boundary this regex searches for the exact word 'sign' in case insensitive mode

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Search a string - whole words only

You can use a Regular Expression, e.g.

WHERE RegExp_Similar(x, '.*\bsign\b.*','i')= 1

As '\b' indicates a word boundary this regex searches for the exact word 'sign' in case insensitive mode

Junior Supporter

Re: Search a string - whole words only

Thanks Dieter, it works. 

.*

What does .* mean here ? Also, it does identify - .SIGN.

--Samir 

Junior Contributor

Re: Search a string - whole words only

'.*' is the same as '%' in LIKE, any number of characters.

And yes, 'syslib.sign' will be match, too.