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!