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\)'; ?
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.
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
-----------------------------------------------------------------------
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:29 EDT