Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: 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
    Ashutosh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could search the string with CHARINDEX function instead of using LIKE.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by pootle flump
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is your current where predicate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by pootle flump
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why use LIKE? Is there a reason you can't search for the literal value?

    Code:
    WHERE FName = '`~!@#$%^&*()[_]+|}{":?><-=\[[]];'',./'
    Otherwise, if you are searching for a string portion, use charindex
    Code:
     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.
    Last edited by pootle flump; 11-11-05 at 09:51.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by pootle flump
    Why use LIKE? Is there a reason you can't search for the literal value?

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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ashutosh9910
    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
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by pootle flump
    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

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ashutosh9910
    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
    If you aren't keen on CHARINDEX and you need to search for portions then perhaps:

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

  11. #11
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by pootle flump
    If you aren't keen on CHARINDEX and you need to search for portions then perhaps:

    Code:
     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

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is an SP yes? So '%`~!@#$%^&*()[_]+|}{":?><-=\[[]];'',./%' is actually passed as a parameter - like @myParam?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •