[thelist] SQL partial field SELECT query
rudy
rudy937 at rogers.com
Fri Oct 10 09:00:20 CDT 2003
> 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.
les, nobody runs ANSI SQL, so you may have to reveal
which database you're on ;o)
however, WHERE productcode LIKE 'A123%' will work in every
database that i'm familiar with
on the other hand, you could use the SUBSTRING function
for the same purpose, seeing as the substring that you
want is at the left
select distinct
substring(productcode from 1 for 4)
, othercolumns
from yourtable
where substring(productcode from 1 for 4) = 'A123'
note that if you do put other columns into a SELECT
with DISTINCT, the DISTINCT applies to all columns,
i.e. it selects distinct rows
also, note that the syntax above for SUBSTRING is ANSI
syntax, which you will have to change to suit your database
rudy
More information about the thelist
mailing list