Handling wildcard characters in query string

Hi

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name

`~!@.#$@.%^&*()[_]+|}{":?><-=\[[]];',./

Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work

Could someone plz help

Thanks in advance
AshutoshYou could search the string with CHARINDEX function instead of using LIKE.

HTH|||You could search the string with CHARINDEX function instead of using LIKE.

HTH

Sorry but did not get your point as how this will help me get through

Ashutosh|||What is your current where predicate?|||What is your current where predicate?

WHERE FName like '%`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./%'

In the front end code I handle it as input params come with %...% qualifiers and ' is replaced by ''

On the sql side i replace _ by [_] and [ by [[]

Ashutosh|||Why use LIKE? Is there a reason you can't search for the literal value?

WHERE FName = '`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./'
Otherwise, if you are searching for a string portion, use charindex
WHERE CHARINDEX('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', FName) >0
HTH

EDIT - you would need to remove the code adding the extra square brackets as you are searching for literals now.|||Why use LIKE? Is there a reason you can't search for the literal value?

WHERE FName = '`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./'
Otherwise, if you are searching for a string portion, use charindex
WHERE CHARINDEX('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', FName) >0
HTH

EDIT - you would need to remove the code adding the extra square brackets as you are searching for literals now.

well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)

Ashutosh|||well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)


AshutoshThere's another reason - sargability. LIKE '%something%' isn't an efficient search if you are looking for the exact string match rather than a portion. It appears that you are looking for one bodge to correct for another.|||There's another reason - sargability. LIKE '%something%' isn't an efficient search if you are looking for the exact string match rather than a portion. It appears that you are looking for one bodge to correct for another.

Probably i have gone the harder way

I created the function to check the same. If there is an exact match, it passes the parameter as string only else it pads the % characters into it

Ashutosh|||well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)


AshutoshIf you aren't keen on CHARINDEX and you need to search for portions then perhaps:

WHERE REPLACE(REPLACE(FName, '%', 'Wild1'), '_', 'Wild2') LIKE REPLACE(REPLACE('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', '%', 'Wild1'), '_', 'Wild2')
It ain't exactly pretty though.|||If you aren't keen on CHARINDEX and you need to search for portions then perhaps:

WHERE REPLACE(REPLACE(FName, '%', 'Wild1'), '_', 'Wild2') LIKE REPLACE(REPLACE('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', '%', 'Wild1'), '_', 'Wild2')
It ain't exactly pretty though.

I di the same but in that case it also replaces the leading and trailing % which I have from the code itself and hense the criteria changes.

%%% --> should come as %[%]%

but it comes as [%][%][%]

Ashutosh|||This is an SP yes? So '%`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./%' is actually passed as a parameter - like @.myParam?