Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Use of wild cards on integer fields |
Wed, Feb 27 2013 1:03 PM | Permanent Link |
John Postnikoff | Is there a way to use wild cards or other SQL method on an DBISAM integer field, and a parametrized query. I am using something like this: 1. This is used on my character field: QryJobReference.Params[1].AsString := '%' + trim (EditJobReference.Text) + '%'; // works great as a character field. SELECT * FROM JobOrders WHERE Custcode = :UCustode and Reference = :UJobRefDescription 2. This statement works. I would like to add a wild card or other SQL method with use of an Integer field somehow. QryJobReference.Params[0].Asinteger := StrtoInt(EditJobNumber.Text); // This is used on my Integer field and works for exact matches SELECT * FROM JobOrders WHERE Custcode = :UCustode and ReferenceNo = :UJobRefNumber I get a DBISAM 11949 parsing error if I try to apply any wildcards, or if I use LIKE in my SQL statement on integer fields. John Postnikoff |
Wed, Feb 27 2013 2:11 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10)) = '%'+:UJobRefNumber+'%' don't know if it will work or not since I haven't tested it. However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN Roy Lambert [Team Elevate] |
Thu, Feb 28 2013 10:36 AM | Permanent Link |
John Postnikoff | To answer your question why anyone would want that. In this case the customer has a very long number to search e.g. 999001054. What I would really like to accomplish is to be able to search a likeness to the series of the last four or five numbers. Now that I think about it, a trimmed string var would likely be the way to go. Thanks for shedding some light to the matter. John Postnikoff. Roy Lambert wrote: John I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10)) = '%'+:UJobRefNumber+'%' don't know if it will work or not since I haven't tested it. However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN Roy Lambert [Team Elevate] |
Thu, Feb 28 2013 10:36 AM | Permanent Link |
John Postnikoff | To answer your question why anyone would want that. In this case the customer has a very long number to search e.g. 999001054. What I would really like to accomplish is to be able to search a likeness to the series of the last four or five numbers. Now that I think about it, a trimmed string var would likely be the way to go. Thanks for shedding some light to the matter. John Postnikoff. Roy Lambert wrote: John I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10)) = '%'+:UJobRefNumber+'%' don't know if it will work or not since I haven't tested it. However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN Roy Lambert [Team Elevate] |
Thu, Feb 28 2013 11:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
I may be teaching granny to suck eggs here but 999001054 looks like a number with built in significance (eg 999 could be a style number or product range) rather than a simple series. If you have the option it might be worth looking at splitting it up for storage purposes which would make it easier for searching. Roy Lambert |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |