INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with LIKE query

Help with LIKE query

(OP)
Hi,

I have the following table and want to look for a phone extension in column Poste_Autorise but only return a true value if something is found.

I did this query that does not work because in my app if there's an empty value between the %% it does return 1 anyway.
I want the LIKE statement to return 0 if the extension is not found.

CODE --> sql

SELECT COUNT(1)FROM Poste_Restriction WHERE Poste_Autorise LIKE '%2243%' AND Poste_Appel_Entrant = '2882' 

Return 1


CODE --> sql

SELECT COUNT(1)FROM Poste_Restriction WHERE Poste_Autorise LIKE '%%' AND Poste_Appel_Entrant = '2882' 

Return 1 (I want it to return 0 because the extension do not exist in Poste_Autorise)

My table:



RE: Help with LIKE query

every string matches via LIKE '%' or LIKE '%%', so no wonder you get all rows with this.

Looking for NON matches means using NOT LIKE '%2243%', - that is not having the extension 2243 anywhere in the list.

If you want to find an entry without any extension you look for empty Poste_Autorise. There is no like pattern needed for that, that's simply Poste_Autorise=''

Bye, Olaf.

RE: Help with LIKE query

(OP)
But I do not want to find an entry without extension, I just want to make sure that if my app is not putting anything between the %% I do not get a 1 from the count.

RE: Help with LIKE query

Well, a filter a user enters should normally bring up something containing the entered value, so simply LIKE '%'+userinput+'%' - let's not talk about user input sanitation at this point and assume that's done.

If you enter no filter, you then get all instead of none, as you don't filter. If you want an empty input to not contribute to found values then it should not limit results to none, it should not limit results any further filters already limit, so LIKE '%%' would be valid for that and show all.

You typically combine this with further filters with AND, to shrink down the result, or with OR to expand the result, matter of taste and settings of a typical filter form. But if there is no input whatsoever I'd not at all let this get to the query, you should simply display a message to the user that a search without any search term will not be done and go back to the filter form.

Bye, Olaf.

RE: Help with LIKE query

(OP)
It is not a gui interface, but I guess I will have to find a way to do it differently.
Thank you for your help.

RE: Help with LIKE query

You actually then know you get 0, so why do the query at all? If you absolutely want 0, then put in anything not existing LIKE '%ZZZZ%'.

Bye, Olaf.

RE: Help with LIKE query

(OP)
It's probably what I'll do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close