0

Does anyone know how to search for whole words using '%[^a-zA-Z]Word[^a-zA-Z]%' and find the words if there is no text after it?

If I use the code below, I don't get the 'Success' message. if PATINDEX(('%[^A-Za-z]' + 'word' + '[^A-Za-z]%'),'Find the word') > 0 select 'Success'

But if use this code below, I do get the 'Success' message. if PATINDEX(('%[^A-Za-z]' + 'word' + '[^A-Za-z]%'),'Find the word go') > 0 select 'Success'

It will only match the words that have text before or after it.

user2355914
  • 1
  • 1

2 Answers2

2
\bdog\b

Should do it. Provided there is a word boundary at both edges...

eidsonator
  • 1,319
  • 2
  • 11
  • 25
  • 1
    Only if they enable CLR and use proper regular expressions rather than the pattern syntax in the question. The regex tag was not added by the OP. – Martin Smith May 06 '13 at 20:36
  • The Patindex is required for other parts of code in the proc previously. I just took out a small chunk to ask the question. – user2355914 May 07 '13 at 13:38
0

... just to connect the dots, based on this answer https://stackoverflow.com/a/5444529:

When the column has a _CI (case insensitive) collation:

patindex('%[^a-z]word[^a-z]%', '.' + Column + '.') > 0

When the column has a _CS (case sensitive) collation (or might):

patindex('%[^a-zA-Z][wW][oO][rR][dD][^a-zA-Z]%', '.' + Column + '.') > 0
Mark Cranness
  • 833
  • 8
  • 6