prefixing a query result

[Dave]

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

[strawberry]

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? :-(

[Dave]

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" <za*******@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.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? :-(

[Jeff North]

On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
<da***@nospam.co.uk>
<uK*******************@newsfe4-gui.ntli.netwrote:

>| 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).
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[strawberry]

Jeff North wrote:

On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
<da***@nospam.co.uk>
<uK*******************@newsfe4-gui.ntli.netwrote:

| 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).
---------------------------------------------------------------
jn******@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;

[strawberry]

strawberry wrote:

Jeff North wrote:

On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
<da***@nospam.co.uk>
<uK*******************@newsfe4-gui.ntli.netwrote:

>| 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).
---------------------------------------------------------------
jn******@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.

[Jeff North]

On 18 Sep 2006 09:11:14 -0700, in mailing.database.mysql "strawberry"
<za*******@gmail.com>
<11*********************@m73g2000cwd.googlegroups. comwrote:

>|
| Jeff North wrote:
| On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "Dave"
| <da***@nospam.co.uk>
| <uK*******************@newsfe4-gui.ntli.netwrote:
| >
| | 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).
| ---------------------------------------------------------------
| jn******@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 :-)
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------