Help with LIKE query

[chipig]

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.

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

Return 1

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:

 

[Olaf Doschke]

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.

 

[chipig]

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.

 

[Olaf Doschke]

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.

 

[chipig]

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.

 

[Olaf Doschke]

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.

 

[chipig]

It's probably what I'll do.