[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


More information about the thelist mailing list