Board index » delphi » Help: SQL pattern matching

Help: SQL pattern matching

Quote
Craig Manley wrote in message <35A9518B.4...@skybound.demon.nl>...
>Hi,
>Does anybody know how you can select fields from a table that contain a
>% character or a _ character? These characters are used for pattern
>matching, so if you want to select a field that contains one of those
>characters you've somehow got to override the pattern matching.

>See these wrong examples:
>select * from MyTable where MyField like 'word1_word2'
>select * from MyTable where MyField like '10% per month'

Don't use the word LIKE use =.
I
select * from MyTable where MyField = '10% per month'

        -- Kirk

 

Re:Help: SQL pattern matching


Hi,
Does anybody know how you can select fields from a table that contain a
% character or a _ character? These characters are used for pattern
matching, so if you want to select a field that contains one of those
characters you've somehow got to override the pattern matching.

See these wrong examples:
select * from MyTable where MyField like 'word1_word2'
select * from MyTable where MyField like '10% per month'

Greetings,
Craig Manley

Re:Help: SQL pattern matching


Quote
kroma wrote:

> Craig Manley wrote in message <35A9518B.4...@skybound.demon.nl>...
> >Hi,
> >Does anybody know how you can select fields from a table that contain a
> >% character or a _ character? These characters are used for pattern
> >matching, so if you want to select a field that contains one of those
> >characters you've somehow got to override the pattern matching.

> >See these wrong examples:
> >select * from MyTable where MyField like 'word1_word2'
> >select * from MyTable where MyField like '10% per month'

> Don't use the word LIKE use =.
> I
> select * from MyTable where MyField = '10% per month'

>         -- Kirk

Hi,
I gave a poor example. What if you want to select all fields containing
the substring '10% per'. You'ld have to do something like this but
somehow substitute the % symbol by something else:
select * from MyTable where MyField like '%10% per%'
- Craig.

Re:Help: SQL pattern matching


On Mon, 13 Jul 1998 09:01:10 +0200, Craig Manley

Quote
<cr...@skybound.demon.nl> wrote:
> I gave a poor example. What if you want to select all fields containing
> the substring '10% per'. You'ld have to do something like this but
> somehow substitute the % symbol by something else:
> select * from MyTable where MyField like '%10% per%'

In MS SQL Server, there is: SUBSTRING (expression, start, length)

But I think that's rather inefficient: Yes, you save the bandwidth of
pumping all the data to a local workstation, and the local processing
power. But it always does a full table scan, I think.

Artur R's suggestion of looking up which special characters let you
insert a literal character into your LIKE clause is probably better.

   Christian R. Conrad

--
All my opinions are _my_own_ and not those of my employer, which is Hedengren,
in Finland. BTW, country codes are two letters and e-mail addresses lowercase.
==============================================================================
     "Wave goodbye to RISC. It's the rotary engine of computing."
            Brad Aisa, in comp.lang.pascal.delphi.advocacy

Re:Help: SQL pattern matching


On Mon, 13 Jul 1998 14:55:45 GMT,
christian.con...@myEmployer.itsCountry (Christian R. Conrad) wrote:

Quote
>On Mon, 13 Jul 1998 09:01:10 +0200, Craig Manley
><cr...@skybound.demon.nl> wrote:

>> I gave a poor example. What if you want to select all fields containing
>> the substring '10% per'. You'ld have to do something like this but
>> somehow substitute the % symbol by something else:
>> select * from MyTable where MyField like '%10% per%'

Classical problem, classical solution: replace each % you want to look
for by two % signs, then put %s around it:

Looking for '10% per'
First step: '10%% per'
Last step: '%10%% per%'

So, your query is:

select * from MyTable where MyField like '%10%% per%'

(At least, every database I know of supports this syntax)

Happy Delphi
Bart

Other Threads