Обсуждение: Query with Parameters and Wildcards
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. -- View this message in context: http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p23248274.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Apr 26, 2009, at 7:21 PM, 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.
Why didn't it work? Any error message), or no result?
Are you expecting case-insensitivity (try ILIKE)
regards, Ries van Twisk
-------------------------------------------------------------------------------------------------
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries@vantwisk.nl
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196
SIP: +1-747-690-5133
On Sun, Apr 26, 2009 at 6:21 PM, landsharkdaddy <ldodd@landsharksoftware.com> 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. Have you tried: SELECT * FROM Customers WHERE FirstName LIKE 'custfirst%'; What does the @ do in sql server?
I have not tried that but I will in the morning. The @ in SQL is used to indicate a parameter passed to the query. In PostgreSQL it seems that the : is the same as the @ in SQL Server. I tried something like: SELECT * FROM Customers WHERE FirstName LIKE :custfirst + '%'; And it told me that the + could not be used. Not sure the exact message but I will check again tomorrow and see what it was and post the results. Scott Marlowe-2 wrote: > > On Sun, Apr 26, 2009 at 6:21 PM, landsharkdaddy > <ldodd@landsharksoftware.com> 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. > > Have you tried: > > SELECT * FROM Customers WHERE FirstName LIKE 'custfirst%'; > > What does the @ do in sql server? > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.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-tp23248274p23250153.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
landsharkdaddy wrote: > I have not tried that but I will in the morning. The @ in SQL is used to > indicate a parameter passed to the query. In PostgreSQL it seems that the : > is the same as the @ in SQL Server. I tried something like: > > SELECT * FROM Customers WHERE FirstName LIKE :custfirst + '%'; > > And it told me that the + could not be used. Not sure the exact message but > I will check again tomorrow and see what it was and post the results. T-SQL defines that variables need to start with @ (like, for instance, in PHP they star with $). In postgres you have positional parametars, $1, for instance. You could, for instance, write SQL function in postgres that would do what you need: CREATE FUNCTION get_customers_with_like (a_name_part character varying) RETURNS SETOF customers AS $$ SELECT * FROM customers WHERE firstname LIKE $1 || '%'; $$ LANGUAGE 'sql'; In postgres, you use '||' for string concatenation (instead of '+' in T-SQL). Mario
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. > -- View this message in context: http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p23257346.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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@landsharksoftware.com> To: <pgsql-sql@postgresql.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
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@landsharksoftware.com> > To: <pgsql-sql@postgresql.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
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@landsharksoftware.com> >> To: <pgsql-sql@postgresql.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@postgresql.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-tp23248274p23260790.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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@landsharksoftware.com> > >> To: <pgsql-sql@postgresql.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@postgresql.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.