Board index » delphi » Simple SQL syntax question

Charles Bédard

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Simple SQL syntax question

How would i specify to retrieve records that have a STRING field containing
a certain substring?

For example: i need to get all the records for which FieldString contains
"test"

SELECT * FROM table WHERE ______________________ (fill in the blank)

i just don't know the operator to test for substring presence.The
SUBSTRING(FieldString FROM 1) doesn't help, as the substring can be
anywhere within the STRING field...

Any help would be appreciated. :)

Please reply by email as well as to this newsgroup.
--
Charles Bedard
beda...@videotron.ca

 
?×???× eá×?ì ?×?????×é

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Charles Bdard <beda...@videotron.ca> D?? ???
<01bc81db$156cbd00$5868fdcf@charles>...

Quote

> How would i specify to retrieve records that have a STRING field
containing
> a certain substring?

> For example: i need to get all the records for which FieldString contains
> "test"

> SELECT * FROM table WHERE ______________________ (fill in the blank)

> i just don't know the operator to test for substring presence.The
> SUBSTRING(FieldString FROM 1) doesn't help, as the substring can be
> anywhere within the STRING field...

> Any help would be appreciated. :)

> Please reply by email as well as to this newsgroup.
> --
> Charles Bedard
> beda...@videotron.ca

Hi !!!!

My name is Paul.

Try this way:

 Select *
  From <table>
  Where <field> like "%test%"

With best wishes Paul E. Shvedov ( shve...@diasoft.ru )

JULIEN_P9

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


try
select * from TABLE
where STRINGFIELD like '%test%'

Charles Bdard <beda...@videotron.ca> a crit dans l'article
<01bc81db$156cbd00$5868fdcf@charles>...

Quote

> How would i specify to retrieve records that have a STRING field
containing
> a certain substring?

> For example: i need to get all the records for which FieldString contains
> "test"

> SELECT * FROM table WHERE ______________________ (fill in the blank)

> i just don't know the operator to test for substring presence.The
> SUBSTRING(FieldString FROM 1) doesn't help, as the substring can be
> anywhere within the STRING field...

> Any help would be appreciated. :)

> Please reply by email as well as to this newsgroup.
> --
> Charles Bedard
> beda...@videotron.ca

Marc Scheun

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Quote
"Charles Bdard" <beda...@videotron.ca> wrote:
>How would i specify to retrieve records that have a STRING field containing
>a certain substring?

Use the SQL wildcards :   "%" (percent sign) stands for any number of
characters (like the * under DOS), and "_" (the underscore) stands for
exactly one character.

Use them in conjunction with the LIKE operator:

        select * from table
        where  field like 'Something%'

        select * from table
        where field like 'Something_'

HTH
Marc

======================================================================
[ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
======================================================================

Malesevic Dusa

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Quote

> SELECT * FROM table WHERE ______________________ (fill in the blank)

  select * from table where stringfield like "test*"
Marc Scheun

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Quote
"Malesevic Dusan" <k...@eunet.yu> wrote:

>> SELECT * FROM table WHERE ______________________ (fill in the blank)

>  select * from table where stringfield like "test*"

Sorry to say, but this is plain wrong, at least in all the SQL
versions I know of.

SQL uses the "%" (percent sign) for any number of characters (like the
* in DOS), and the "_" (underscore) for exactly one character.

Marc

======================================================================
[ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
======================================================================

The Graphical Gno

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Quote
In article <33b756e5.4355...@news.bernoise.ch>, marc.scheu...@NO.SPAM.PLEASE.ch (Marc Scheuner) wrote:
>"Malesevic Dusan" <k...@eunet.yu> wrote:

>>> SELECT * FROM table WHERE ______________________ (fill in the blank)

>>  select * from table where stringfield like "test*"

>Sorry to say, but this is plain wrong, at least in all the SQL
>versions I know of.

>SQL uses the "%" (percent sign) for any number of characters (like the
>* in DOS), and the "_" (underscore) for exactly one character.

There are versions of SQL where it is "*". One of then is ACCESS. It depends
on the driver you are using.

This sucker did slow me down for about three weeks.

The Graphical Gnome   (r...@ktibv.nl)

Senior Software Engineer

--------------------------------------------------------------------------------------------------------------
Delphi developers FAQ at http://www.gnomehome.demon.nl/uddf/index.htm
Non frame version     at http://www.gnomehome.demon.nl/uddf/indexnf.htm
Delphi Beautifier     at http://www.gnomehome.demon.nl/xrefpas/xrefpas.zip    

Marc Scheun

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


r...@ktibv.nl (The Graphical Gnome) wrote:

Quote
>>SQL uses the "%" (percent sign) for any number of characters (like the
>>* in DOS), and the "_" (underscore) for exactly one character.

>There are versions of SQL where it is "*". One of then is ACCESS. It depends
>on the driver you are using.

Well, I guess that's because ACCESS is not a full-blown SQL database,
but a DOS/Windows-based "database" product.

Of the true SQL products (I mean a full-fledged RDBMS, like Oracle,
Sybase, SQL Server, Interbase, Informix, DB2 and whatever else there
is) I don't know any that uses * and ?

Marc

======================================================================
[ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
======================================================================

Peter G. Milla

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Simple SQL syntax question


Use the LIKE operator.
EG:
SELECT * FROM table WHERE FieldString Like "%test%";

Using the % wildcards should match the string anywhere in the field.

In article <01bc81db$156cbd00$5868fdcf@charles>, "Charles Bdard"

Quote
<beda...@videotron.ca> wrote:

>How would i specify to retrieve records that have a STRING field containing
>a certain substring?
>For example: i need to get all the records for which FieldString contains
>"test"
>SELECT * FROM table WHERE ______________________ (fill in the blank)

*************************************************************************
* Peter G. Millard      ******************* Maintainer of the VB        *
* mill...@buffnet.net          **************  General & VBDOS FAQ's      *
* http://www.buffnet.net/~millard  ******** Error Reading Drive A.....      *
* http://www.vantek-corp.com             **     Formatting C: Instead    *
*************************************************************************

Other Threads

1. A simple syntax question...

2. Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

3. Simple Local SQL Syntax

4. Newbie question: SQL syntax

5. SQL syntax question

6. SQL Syntax question

7. Oracle 8 Syntax Question (vs. MS SQL)

8. SQL Syntax Question

9. SQL syntax questions

10. SQL Syntax error question