prefixing a query result

Is it possible within the sql statement to prefix with a piece of text?

For example, if i had a table with a column firstname and lastname, and a
sql statemtn like

select firstname,lastname from tblnames

could i have the sql return something like "Mr John Smith" so it is all in
one string?

Thanks for any help

Dave

Re: prefixing a query result

Dave wrote:
> Is it possible within the sql statement to prefix with a piece of text?
>
> For example, if i had a table with a column firstname and lastname, and a
> sql statemtn like
>
> select firstname,lastname from tblnames
>
> could i have the sql return something like "Mr John Smith" so it is all in
> one string?
>
> Thanks for any help
>
> Dave

Well, you could:

SELECT CONCAT('Mr ',firstname,' ',lastname) FROM tblnames;

But that kind of defeats the purpose of a database, doesn't it? :-(

Re: prefixing a query result

Thanks for that.

I don't think it defeats it really, it just saves me writing that logic
elsewhere, but still leaves the structure there so I can query just for
first names, or sort by last name.

"strawberry" wrote in message
news:1158591004.835799.201290@m7g2000cwm.googlegroups.com...
>
> Dave wrote:
>> Is it possible within the sql statement to prefix with a piece of text?
>>
>> For example, if i had a table with a column firstname and lastname, and a
>> sql statemtn like
>>
>> select firstname,lastname from tblnames
>>
>> could i have the sql return something like "Mr John Smith" so it is all
>> in
>> one string?
>>
>> Thanks for any help
>>
>> Dave
>
> Well, you could:
>
> SELECT CONCAT('Mr ',firstname,' ',lastname) FROM tblnames;
>
> But that kind of defeats the purpose of a database, doesn't it? :-(
>

Re: prefixing a query result

On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"

wrote:

>| Is it possible within the sql statement to prefix with a piece of text?
>|
>| For example, if i had a table with a column firstname and lastname, and a
>| sql statemtn like
>|
>| select firstname,lastname from tblnames
>|
>| could i have the sql return something like "Mr John Smith" so it is all in
>| one string?
>|
>| Thanks for any help
>|
>| Dave

What happens if it is a female your responding to?
I think it would be more preferable to do
SELECT concat(firstname,' ',lastname) as FullName, Gender FROM myTable
Then in code add the prefix according to gender (you might also want
to add an age range so you can have Master and Miss as a salutation).
------------------------------------------------------------ ---
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
------------------------------------------------------------ ---

Re: prefixing a query result

Jeff North wrote:
> On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
>
> wrote:
>
> >| Is it possible within the sql statement to prefix with a piece of text?
> >|
> >| For example, if i had a table with a column firstname and lastname, and a
> >| sql statemtn like
> >|
> >| select firstname,lastname from tblnames
> >|
> >| could i have the sql return something like "Mr John Smith" so it is all in
> >| one string?
> >|
> >| Thanks for any help
> >|
> >| Dave
>
> What happens if it is a female your responding to?
> I think it would be more preferable to do
> SELECT concat(firstname,' ',lastname) as FullName, Gender FROM myTable
> Then in code add the prefix according to gender (you might also want
> to add an age range so you can have Master and Miss as a salutation).
> ------------------------------------------------------------ ---
> jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
> ------------------------------------------------------------ ---

yes, exactly - although it's possible that the OP's statement was just
an example

personally, if i thought that it was going to be important to include
information like this then I'd put it in the db to begin with, and then
use CONCAT_WS (which handles null results more elegantly) like so:

SELECT CONCAT_WS(' ',title,',firstname,lastname) from tblnames;

Re: prefixing a query result

strawberry wrote:
> Jeff North wrote:
> > On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
> >
> > wrote:
> >
> > >| Is it possible within the sql statement to prefix with a piece of text?
> > >|
> > >| For example, if i had a table with a column firstname and lastname, and a
> > >| sql statemtn like
> > >|
> > >| select firstname,lastname from tblnames
> > >|
> > >| could i have the sql return something like "Mr John Smith" so it is all in
> > >| one string?
> > >|
> > >| Thanks for any help
> > >|
> > >| Dave
> >
> > What happens if it is a female your responding to?
> > I think it would be more preferable to do
> > SELECT concat(firstname,' ',lastname) as FullName, Gender FROM myTable
> > Then in code add the prefix according to gender (you might also want
> > to add an age range so you can have Master and Miss as a salutation).
> > ------------------------------------------------------------ ---
> > jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
> > ------------------------------------------------------------ ---
>
> yes, exactly - although it's possible that the OP's statement was just
> an example
>
> personally, if i thought that it was going to be important to include
> information like this then I'd put it in the db to begin with, and then
> use CONCAT_WS (which handles null results more elegantly) like so:
>
> SELECT CONCAT_WS(' ',title,',firstname,lastname) from tblnames;

Oh, I made a typo there - but you get the idea.

Re: prefixing a query result

On 18 Sep 2006 09:11:14 -0700, in mailing.database.mysql "strawberry"

<1158595874.417156.28960@m73g2000cwd.googlegroups.com> wrote:

>|
>| Jeff North wrote:
>| > On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
>| >
>| > wrote:
>| >
>| > >| Is it possible within the sql statement to prefix with a piece of text?
>| > >|
>| > >| For example, if i had a table with a column firstname and lastname, and a
>| > >| sql statemtn like
>| > >|
>| > >| select firstname,lastname from tblnames
>| > >|
>| > >| could i have the sql return something like "Mr John Smith" so it is all in
>| > >| one string?
>| > >|
>| > >| Thanks for any help
>| > >|
>| > >| Dave
>| >
>| > What happens if it is a female your responding to?
>| > I think it would be more preferable to do
>| > SELECT concat(firstname,' ',lastname) as FullName, Gender FROM myTable
>| > Then in code add the prefix according to gender (you might also want
>| > to add an age range so you can have Master and Miss as a salutation).
>| > ------------------------------------------------------------ ---
>| > jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
>| > ------------------------------------------------------------ ---
>|
>| yes, exactly - although it's possible that the OP's statement was just
>| an example
>|
>| personally, if i thought that it was going to be important to include
>| information like this then I'd put it in the db to begin with, and then
>| use CONCAT_WS (which handles null results more elegantly) like so:
>|
>| SELECT CONCAT_WS(' ',title,',firstname,lastname) from tblnames;

My sentiments exactly :-)
------------------------------------------------------------ ---
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
------------------------------------------------------------ ---