Click here to get back home

prefixing a query result
 Home | NewsGroups | Search | About
Login Password
Register Now!
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
prefixing a query result Dave 09-18-2006
Get Chitika Premium
Posted by Dave on September 18, 2006, 10:07 am
Please log in for more thread options
 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



Posted by strawberry on September 18, 2006, 10:50 am
Please log in for more thread options
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? :-(


Posted by Dave on September 18, 2006, 10:56 am
Please log in for more thread options
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.

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



Posted by Jeff North on September 18, 2006, 11:36 am
Please log in for more thread options
On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "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

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
---------------------------------------------------------------

Posted by strawberry on September 18, 2006, 12:11 pm
Please log in for more thread options

Jeff North wrote:
> On Mon, 18 Sep 2006 14:07:22 GMT, in mailing.database.mysql "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
>
> 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;


Similar ThreadsPosted
Different result from query made in mysql-front and from ASP June 8, 2006, 7:51 am
getting the last result May 26, 2006, 12:11 pm
return result set in SPs August 23, 2005, 1:37 am
mysql damage result set June 12, 2006, 6:42 am
how to get result set from calling procedure? August 15, 2006, 5:39 am
Adding GROUP BY empties the result set? November 12, 2005, 12:17 pm
Your Online Lottery Winning Result Batch Number: P2/0056/2008 November 27, 2008, 12:54 pm
Query help: Get category and number of items in one query June 25, 2005, 1:36 am
Update query needed - on a "group by" query - Possible / How??? January 22, 2006, 3:43 pm
Help with an SQL query? September 4, 2005, 1:43 pm

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap