Peter H. Coffin <hellsop@ninehells.com> wrote:


> "*" is not a LIKE wildcard. "%" and "_" are LIKE wildcards.
>

my query is:
rs.open "select artista, substring(artista, locate(artista, ' ') as
surname , online from artisti where online = 'S' having surname like
'W%' order by surname asc" , conn

but "Andy Warhold" is in the database in the ARTISTA field.
where's the problem?
thanks, Ale.

Ale Stanga <alessandro.stanga@gmail.com> wrote:

>
> but "Andy Warhold" is in the database in the ARTISTA field.
> where's the problem?
> thanks, Ale.

and the recordest returned by the query is empty.

On 1 Oct, 18:25, alessandro.sta...@gmail.com (Ale Stanga) wrote:
> Peter H. Coffin <hell...@ninehells.com> wrote:
>
> > "*" is not a LIKE wildcard. "%" and "_" are LIKE wildcards.
>
> my query is:
> rs.open "select artista, substring(artista, locate(artista, ' ') as
> surname , online from artisti where online = 'S' having surname like
> 'W%' order by surname asc" , conn
>
> but "Andy Warhold" is in the database in the ARTISTA field.
> where's the problem?
> thanks, Ale.

Wouldn't it be easier to say

SELECT
artista,
substring(artista, locate(artista, ' ') as surname,
online
FROM artisti
WHERE online = 'S' AND artista LIKE '% W%'
ORDER BY surname ASC

On 1 Oct, 18:25, alessandro.sta...@gmail.com (Ale Stanga) wrote:
> Peter H. Coffin <hell...@ninehells.com> wrote:
>
> > "*" is not a LIKE wildcard. "%" and "_" are LIKE wildcards.
>
> my query is:
> rs.open "select artista, substring(artista, locate(artista, ' ') as
> surname , online from artisti where online = 'S' having surname like
> 'W%' order by surname asc" , conn
>
> but "Andy Warhold" is in the database in the ARTISTA field.
> where's the problem?
> thanks, Ale.

There are 3 mistakes here:

1) The function "substring(artista, locate(artista, ' ')" has a
closing bracket missing. You should have had a syntax error raised!

2) The function "SUBSTRING(artista, LOCATE(artista, ' '))" has the
paramters for LOCATE round the wrong way. It should be
"SUBSTRING(artista, LOCATE( ' ',artista))"

3) The function "SUBSTRING(artista, LOCATE( ' ',artista))" will
include the space in its result and so the LIKE 'W%' will fail,
because it doesn't taek into account the leading space. Thus teh
function "SUBSTRING(artista, LOCATE( ' ',artista))" should read
"SUBSTRING(artista, LOCATE( ' ',artista)+1)"

But apart from the fact that there were so many mistakes, it was
perfectly correct!

Captain Paralytic <paul_lautman@yahoo.com> wrote:

> 3) The function "SUBSTRING(artista, LOCATE( ' ',artista))" will
> include the space in its result and so the LIKE 'W%' will fail,
> because it doesn't taek into account the leading space. Thus teh
> function "SUBSTRING(artista, LOCATE( ' ',artista))" should read
> "SUBSTRING(artista, LOCATE( ' ',artista)+1)"
>
> But apart from the fact that there were so many mistakes, it was
> perfectly correct!

fantastic!!
thanks a lot!
now all works fine!