Re: Query with Parameters and Wildcards

Hope I got your question right and I will somehow manage to explain it in a
simple way.

SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 || '%')

Here you use $1 which is the position parameter in the function. So if you
create the function as CREATE FUNCTION test(par1 varchar, par2 varchar) then $1
represents the first parameter and $2 the second one. You can't use position
parameters outside of functions.

For your needs a normal query would do it. The query that's send to the server
would need to look something like this:
SELECT * FROM lanemanger.customers WHERE (customerfirstname
ILIKE 'lastname' || '%')
lastname would be the one you're searching for.

Now I have a question about your original query:
SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%';

As much as I know @ in mssql is used only inside of procedures and since you're
using C# isn't @custfirst in the end a C# variable and has nothing to do with
the sql server?
If I'm right with that then wouldn't the query for postgres look like:
SELECT * FROM Customers WHERE FirstName ILIKE (@custfirst || '%');

Could be you will have to enclose the @custfirst in single quotas to make it
work but how to do it right depends on the programming language you use.

Regards,
Jure

On Monday 27 of April 2009 19:11:09 landsharkdaddy wrote:
> That is it! It works just fine and from my Typed Dataset in c# I just call
> the function using SELECT * FROM
> lanemanager.GetCustomerByLastName(:customerlastname) and it works
> perfectly. Through this whole process I have also gained a greater
> understanding of using Functions as well. I guess I would like to know why
>
> SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 ||
> '%')
>
> doesnt work when used as the select query of the dataset. Using functions
> is just as well but I would like to understand why the query doesnt work
> unless it is called from a function. Thanks to all that helped with this
> one, you have been very helpful.
>
> Jure Kobal wrote:
> > In the original select you missed a small part. The operation || needs a
> > value
> > on every side and you missed the value on the left side. You had it as:
> > WHERE
> > (customerlastname ILIKE || '%') instead of WHERE (customerlastname
> > ILIKE 'lastname' || '%'). And that is the reason for the error you got.
> >
> > The function from dayat has a small mistake. It should be:
> > CREATE OR REPLACE FUNCTION TEST(lastname VARCHAR)
> > else it will result in an error because of the missing space.
> >
> > On Monday 27 of April 2009 16:47:40 dayat wrote:
> >> Do you run this code in the function? If so, the following example
> >> function
> >> with LANGUAGE SQL function:
> >>
> >> CREATE OR REPLACE FUNCTION TEST(lastnameVARCHAR)
> >> RETURNS SETOF lanemanager.customers
> >> AS $$
> >> SELECT customercellphone, customercity, customerdatecreated,
> >> customerdatelastmodified, customeremail, customerfax, customerfirstname,
> >> customerid, customerlastname, customermiddleinitial, customerphone,
> >> customerreferredby, customerstateabbr, customerstreet1, customerstreet2,
> >> customersuffix, customertitle, customerworkphone, customerworkphoneext,
> >> customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1
> >>
> >> '%')
> >> $$
> >> LANGUAGE SQL;
> >>
> >> Please tell me is it work for you.
> >>
> >> Regards
> >> Hidayat
> >>
> >> ----- Original Message -----
> >> From: "landsharkdaddy" <ldodd(at)landsharksoftware(dot)com>
> >> To: <pgsql-sql(at)postgresql(dot)org>
> >> Sent: Monday, April 27, 2009 9:19 PM
> >> Subject: Re: [SQL] Query with Parameters and Wildcards
> >>
> >> > When I try the following like you suggested I get an error that says
> >> > "operator does not exist: || unknown
> >> >
> >> >
> >> >
> >> > SELECT customercellphone, customercity, customerdatecreated,
> >> > customerdatelastmodified, customeremail, customerfax,
> >>
> >> customerfirstname,
> >>
> >> > customerid, customerlastname, customermiddleinitial, customerphone,
> >> > customerreferredby, customerstateabbr, customerstreet1,
> >>
> >> customerstreet2,
> >>
> >> > customersuffix, customertitle, customerworkphone,
> >> > customerworkphoneext, customerzip FROM lanemanager.customers WHERE
> >> > (customerlastname ILIKE || '%')
> >> >
> >> > landsharkdaddy wrote:
> >> >> I have a query that works on SQL Server to return customers that
> >>
> >> contain
> >>
> >> >> the string entered by the user by accepting parameters and using the
> >> >> LIKE keyword. I would like to move this to postgreSQL but I'm just
> >> >> not sure how
> >> >> to get it done. This is the query
> >> >>
> >> >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%';
> >> >>
> >> >> This works great on SQL Server but not on postgreSQL. Any help would
> >>
> >> be
> >>
> >> >> appreciated.
> >>
> >> __________________________________________________
> >> Apakah Anda Yahoo!?
> >> Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik
> >> terhadap spam http://id.mail.yahoo.com
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> View this message in context:
> http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p232607
>90.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.