[thelist] SQL partial field SELECT query

[Les Lytollis]

Hi All
 
I need help with an ANSI SQL SELECT query - or maybe there is another
easier way I am missing.
 
I am building a web front end to our SOP system for online ordering.
 
The products are clothes and obviously come in a range of sizes. So I
would expect a user to see a catalogue of shirts ,trousers, etc, select
a garment, then select the required size, colour, etc.
 
 
However, because of the way the data is held in the legacy system, each
garment / size / colour variant has its own product code which
implicitly holds this info and the data is not normalised.
 
For example, Ladies Navy Skirt has a "stock code" of A123, but each
product has a "product code" such as A123-12S, A123-12R, A123-12L and
the stock code is not held anywhere in the DB. 
 
SO if I want to get a "sizeless" product I would have to parse the
product code to get only the first four characters, then to get sizes
available I would have to parse the field "WHERE productcode LIKE
'A123%'" and capture the "%" bit. 
 
My problem comes in finding a way to SELECT DISTINCT on a partial field
search (the A123 bit) in order to get eg "Ladies Navy Skirt" without
sizes. 
 
Am I missing some simpler option?
 
regards
Lez
 



********************Confidentiality Notice & Disclaimer ***********************
This message, together with any attachments, is for the confidential and exclusive use of the addressee(s).
If you receive it in error, please delete the message and its attachments from your system immediately and notify us by return e-mail.
Do not disclose, copy, circulate or use any information contained in this e-mail.

(1) Whilst we have taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses, we cannot accept liability for any damage sustained as a result of software viruses and would advise that you carry out your own virus checks before opening any attachment.
(2) The sender shall remain solely accountable for any statements, representations or opinions that are clearly his or her own and not made in the course of employment.
***********************************************************************************