Pattern Matching "OR" 1

[codecontractor]

I'm looking for a way to streamline the following stored procedure query using regular expressions:

---------------------------------------
SELECT *
FROM TitleTable
WHERE
Title like @searchString OR
Title like @searchString + 's' OR
Title like @searchString + 'es' OR
Title like @searchString + 'ing' OR
Title like @searchString + 'd' OR
Title like @searchString + 'ed'
---------------------------------------

I was hoping there was something along the lines of:

---------------------------------------
Title like @searchString OR
Title like @searchString + '(s)|(es)|(ing)|(d)|(ed)'
---------------------------------------

(Obviously this doesn't work.)

I have done things like this before with regular expressions in other environments, but I can't find documentation anywhere on how I can do this in T-SQL.

Thanks,
-Ben

 

[dbomrrsm]

SELECT *
FROM TitleTable
WHERE
Title IN (@searchString,
@searchString + 's',
@searchString + 'es',
@searchString + 'ing',
@searchString + 'd',
@searchString + 'ed')

[blue]DBomrrsm[/blue]

 

[codecontractor]

Thank you! That almost works.

However, I should have mentioned in my first post that I have to use regular expressions because these aren't exact matches.

I have tested a few regular expressions within the context of IN, but it doesn't seem to be evaluating them...

This is what I tried to do:

SELECT *
FROM
WHERE
Title IN (
@searchString + '[^a-z]%',		
@searchString,		
@searchString + 's[^a-z]%',
@searchString + 's',
'%[^a-z]' + @searchString + '[^a-z]%',
'%[^a-z]' + @searchString,
'%[^a-z]' + @searchString + 's[^a-z]%',
'%[^a-z]' + @searchString + 's
)

Above I attempt to find complete words within a phrase, that is why I exclude any letter characters from the pattern.

If I can use pattern matching within the IN statement that's perfect, but if not, is there another option?

Thanks,
-Ben

 

[dbomrrsm]

SELECT *
FROM
WHERE
Title IN (
'%'+@searchString + '[^a-z]%',        
'%'+@searchString+'%',        
'%'+@searchString + 's[^a-z]%',
'%'@searchString + 's',
'%[^a-z]' + @searchString + '[^a-z]%',
'%[^a-z]' + @searchString,
'%[^a-z]' + @searchString + 's[^a-z]%',
'%[^a-z]' + @searchString + 's
)

try that but if it doesnt work try playing around with '%'.

[blue]DBomrrsm[/blue]

 

[codecontractor]

Ok, I've brought it down to its simplest form, I think. It appears that the wildcards are not getting evaluated. Here is an example.

SELECT     *
FROM       table
WHERE

Title IN (
	
	'%' + @searchString + '%'  -- line in question
)

I have tested that by manually by putting '%a%' in for the 'line in question' and got nothing.

I can't imagine that I should be escaping the '%', so I'm guessing the IN option isn't like LIKE in that it processes regular expressions.

Thanks again,
-Ben

 

[donutman]

If you are willing to accept rows that are returned by "Like '%somestring%' ". Then what is the point of adding suffixes?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

IN does not work the same as LIKE

if @searchString is 'foo', then

WHERE Title IN ( '%' + @searchString + '%' )

evaluates to

WHERE Title IN ( '%foo%' )

and of course this will return no rows unless there's a title which actually is equalt to the value '%foo%' (which is unlikely)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[maswien]

Add column title to full text index, then use CONTAINS in the where clause, following searches for all titles with words of @searchString with suffix -ed, -ing, and so on.

WHERE contains(title, ' FORMSOF (INFLECTIONAL, @searchString) ')

Or,

Weight: a number from 0.0 through 1.0, indicates the degree of importance for each word and phrase within a set of words and phrases.

WHERE CONTAINS(Title, 'ISABOUT (@searchString weight (.8)' )