On Mon, 2004-06-21 at 09:24, Ronan Heffernan wrote:
> Russ Wright wrote:
>
> >Hello All
> >
> >I have an odd problem. I am doing some work for a client and I need to
> >SELECT some rows from a SQL table named PRODUCT based on a PARTNUMBER
> >field. The kicker is that the part number field is a character field
> >and has parenthesis in it like so:
> >
> >PARTNUMBER DESCRIPTION
> >---------- -----------
> >12345(6) widget 1
> >12345(7) widget 2
> >
> >SO my SQL looks like this:
> >SELECT * FROM PRODUCT WHERE PARTNUMBER = '12345(6)'
> >
> >That does not work as () are reserved characters. Any ideas?
> >
> >
> Have you tried 'escaping' the parentheses:
>
> SELECT * FROM PRODUCT WHERE PARTNUMBER = '12345\(6\)'; ?
>
Yes I tried that and it does not work.
> Which database is this? I have never had Postgres complain about keywords or characters inside a quoted string before! This search is almost certainly ANSI SQL compliant and your vendor has screwed this up.
>
Oh sorry I should have mentioned that. I am fixing an existing a Visual
Basic 6.0 interface using ADO through an ODBC driver written by Mini
soft into an HP3000 server running an IMAGE database. IMAGE is some
sort of HP proprietary database. What a mix!
> If you structure of your partnumber is always the same, you could probably use a LIKE operator to get around this problem (but probably with a BIG performance penalty):
>
> SELECT * FROM PRODUCT WHERE PARTNUMBER LIKE '12345%6%';
>
> If your partnumbers can vary in structure, this is risky. The above query would also match:
> 1234579768
> 12345(16)
> etc
>
> --ronan
>
Hmmm. Not sure because the part number could be in the database both
ways. For example:
12345(6)
123456
Not sure if that will work but I will try it. Thanks for your
suggestions! :-)
Regards
Russ
-----------------------------------------------------------------------
This list is provided as an unmoderated internet service by Networked
Knowledge Systems (NKS). Views and opinions expressed in messages
posted are those of the author and do not necessarily reflect the
official policy or position of NKS or any of its employees.
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 17:58:41 EDT