name and surname in a single field

i have a database with a list of artists with a single field for the
name and surname, separated with a space without the problem of the
composite surname (like de fonseca, because who insered the data used an
underscore to concatenate them, like de_fonseca)
now, i have to make an sql query ordered by surname, i'm using mysql and
asp.
how can i divide them?
i saw that the split function works well, i have to do:
splitted = split(namesurnamefield)
surname = splitted(ubound(splitted))
but how can i order the data with the ORDER_BY option?
is it possible?
can i make a virtual field like with DB2 on the AS400?
thanks,
Ale.

On Tue, 4 Sep 2007 20:32:40 +0200, alessandro.stanga@gmail.com
(mcstanga) wrote:

>i have a database with a list of artists with a single field for the
>name and surname, separated with a space without the problem of the
>composite surname (like de fonseca, because who insered the data used an
>underscore to concatenate them, like de_fonseca)
>now, i have to make an sql query ordered by surname, i'm using mysql and
>asp.
>how can i divide them?
>i saw that the split function works well, i have to do:
>splitted = split(namesurnamefield)
>surname = splitted(ubound(splitted))
>but how can i order the data with the ORDER_BY option?
>is it possible?
>can i make a virtual field like with DB2 on the AS400?
>thanks,
>Ale.

Are you sure it's also ok for the given names ? Like the french
Jean-Pierre may sometimes be written with a space instead of a hyphen.
If it is also replaced by _, then use something like :

SELECT SUBSTRING( name , LOCATE( name ,' ' ) ) AS surname
FROM ...
WHERE ...
ORDER BY surname

On 5 Set, 11:24, subtenante <zzsubtenant...@gmail.com> wrote:
> Are you sure it's also ok for the given names ? Like the french
> Jean-Pierre may sometimes be written with a space instead of a hyphen.
> If it is also replaced by _, then use something like :

sure, replaced with _ too :)

> SELECT SUBSTRING( name , LOCATE( name ,' ' ) ) AS surname
> FROM ...
> WHERE ...
> ORDER BY surname

rs.open "select ARTISTA, SUBSTRING( ARTISTA, LOCATE( ARTISTA,' ' ) )
AS surname , ONLINE from artisti where ONLINE = 'S' ORDER BY surname
ASC" , conn

it ignores the ASC and DESC! why??
the records are always ordered by surname in inverted alphabetically
order.
thanks a lot!

subtenante <zzsubtenantezz@gmail.com> wrote:

>
> SELECT SUBSTRING( name , LOCATE( name ,' ' ) ) AS surname
> FROM ...
> WHERE ...
> ORDER BY surname

another question.
can i divide the artists with the first character of theyyre surnames?
something like this:
where surname like a*
like b*
and so on.
i got an error, something that surname is a wrong column in the where
condition...
thank you for you support!

mcstanga wrote:
> subtenante <zzsubtenantezz@gmail.com> wrote:
>
>>
>> SELECT SUBSTRING( name , LOCATE( name ,' ' ) ) AS surname
>> FROM ...
>> WHERE ...
>> ORDER BY surname
>
> another question.
> can i divide the artists with the first character of theyyre surnames?
> something like this:
> where surname like a*
> like b*
> and so on.
> i got an error, something that surname is a wrong column in the where
> condition...
> thank you for you support!

For "dynamic" columns you use the HAVING clause rather than the WHERE one

Paul Lautman <paul.lautman@btinternet.com> wrote:

> For "dynamic" columns you use the HAVING clause rather than the WHERE one

i'm so noob!!
thanks!

Paul Lautman <paul.lautman@btinternet.com> wrote:

> For "dynamic" columns you use the HAVING clause rather than the WHERE one

this doesn't work!
i can't get any record using surname like 'a*'
i've tried also '*a*' '%a%' but i still can't get any record.
strange as the order_by!

mcstanga wrote:
> Paul Lautman <paul.lautman@btinternet.com> wrote:
>
>> For "dynamic" columns you use the HAVING clause rather than the
>> WHERE one
>
> this doesn't work!
> i can't get any record using surname like 'a*'
> i've tried also '*a*' '%a%' but i still can't get any record.
> strange as the order_by!

Could you reword this? I cannot understand what you are trying to say?

Paul Lautman <paul.lautman@btinternet.com> wrote:

> Could you reword this? I cannot understand what you are trying to say?

i can't get any record using the having myfield like '*a'
the recordset remains empty but i'm pretty sure that there are a lot of
myfield that begin or ends with a.

mcstanga wrote:
> Paul Lautman <paul.lautman@btinternet.com> wrote:
>
>> Could you reword this? I cannot understand what you are trying to
>> say?
>
> i can't get any record using the having myfield like '*a'
> the recordset remains empty but i'm pretty sure that there are a lot
> of myfield that begin or ends with a.

You may have to do a subquery for this one