[thelist] SQL partial field SELECT query

[Les Lytollis]

Hi Rudy

Thanks for the reply.

No problem "revealing" the database...

I want to import data into SQL Server 2000 using Merant ODBC 3.6 to
query Progress 9.1 on AIX (which has *ANSI SQL 92* support switched on)

...well, you asked!

Lez




-----Original Message-----
From: rudy [mailto:rudy937 at rogers.com] 

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




********************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.
***********************************************************************************