challenging likes wildcard

Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?

Thanks

Spencer

Hi

You could use

SELECT [name] FROM table WHERE REPLACE ([name] , ' ' , '' ) LIKE '%{{apple}}%'

this will remove all spaces for the prurposes of the search query

|||

Thanks Rod,

But this query will search the keyword ap ple , a pple , app le etc. This is the challenge for me.

Spencer

|||

Hi Spencer,

Can you try the below query

SELECT [name]

FROM "table" (nolock)

WHERE [name] LIKE '%' + LTRIM(RTRIM(REPLACE(REPLACE('{{ apple }}','{',''),'}',''))) + '%'

Eralper

http://www.kodyaz.com

|||

You could do something like below:

declare @.a varchar(30), @.b varchar(30), @.c varchar(30)
set @.a = '{{apple }}' -- '{{$apple$}}'
set @.b = substring(@.a, patindex('%{{%apple%}}%', @.a) + 2, 8000)
set @.c = substring(reverse(@.a), patindex(reverse('%{{%apple%}}%'), reverse(@.a)) + 2, 8000)
if @.a like '%{{%apple%}}%' and substring(@.b, 1, patindex('%apple%}}%', @.b) - 1) not like '%[^ ]%'
and substring(@.c, 1, patindex(reverse('%{{%apple%'), @.c) - 1) not like '%[^ ]%'
print 'Y'
else
print 'N'

You can perform this in a SELECT statement by using a single expression. I am assuming that there could be multiple spaces before and after the keyword. So it is not possible to write a single search pattern for LIKE using the TSQL support. The code checks for any non-space character in addition to keyword match.

|||

Thanks Umachandar