MySQL Arbitarary string prefix in select statement

**LordOfLA** · May 1st, 2006

Hey!

Anyone know if I can have mysql 5 arbitrarily prefix a value from a table in a select statement?

I had thought that something like:

Code:

SELECT CONCAT('maildir:',pop) AS home FROM users WHERE username = "email@domain.com"

would do the trick but I just get nothing out of mysql.

Pointers in the right direction would be appreciated

**Geffy** · May 1st, 2006

I too would also expect that to work.

you tried it with the email address in single quotes?

a good way to test it would be to try
SELECT CONCAT('maildir:', pop) AS home FROM users;

**Mainframeguy** · May 1st, 2006

there's no chance this is in a php script and you have perhaps ommitted \"maildir:\" is there? The only other thing I can think of that pop is returning as null - in which case the whole finction evaluates to null (you never get just "maildir:" returned, but then I can't see why you would want to either...

**LordOfLA** · May 1st, 2006

nah not php, configuration for dovecot imap/pop3 server

For the moment I've manually specified a default location for maildir locations, but since that's stored in the database I'd like it to figure it out for itself

However I need to prefix "maildir:" to the value in the database so that insted of /usr/local/mail/domain/user_part_of_full_mail_address/Maildir it gets maildir:/usr/local/mail/domain/user_part_of_full_mail_address/Maildir

**LordOfLA** · May 1st, 2006

Well I don't know what I was doing wrong last night but the following works now:

Code:

SELECT CONCAT('maildir:',pop) AS mail, uid, gid FROM users WHERE username = %u"

%u gets expanded by dovecot to be the full username as supplied at pop3/imap4 login and is a full email address.

My guess is that I was supposed to use mail and not home. But works now

May 1st, 2006	Top \| #1
LordOfLA Godlike! Joined: February 2004 Location: Birmingham, UK Posts: 6,818 Blog Entries: 5 Reputation: 3651 Power: 179	MySQL Arbitarary string prefix in select statement Hey! Anyone know if I can have mysql 5 arbitrarily prefix a value from a table in a select statement? I had thought that something like: Code: SELECT CONCAT('maildir:',pop) AS home FROM users WHERE username = "email@domain.com" would do the trick but I just get nothing out of mysql. Pointers in the right direction would be appreciated
	If HK-47 and GLaDOS had a child, the character they create would cause the video game world to overdose on awesome. -sheridanmovieguy: Dragon age forum user.

May 1st, 2006	Top \| #2
Geffy OSNN Veteran Addict Joined: March 2002 Location: United Kingdom Posts: 7,854 Reputation: 1490 Power: 190	Re: MySQL Arbitarary string prefix in select statement I too would also expect that to work. you tried it with the email address in single quotes? a good way to test it would be to try SELECT CONCAT('maildir:', pop) AS home FROM users;
	 • blog • tumblog • lastfm • flickr • #rubyonrails • @twitter "I could be replaced with a very small shell script"

May 1st, 2006	Top \| #3
Mainframeguy Debiant by way of Ubuntu Joined: August 2002 Location: London, UK Posts: 3,758 Blog Entries: 5 Reputation: 1390 Power: 142	Re: MySQL Arbitarary string prefix in select statement there's no chance this is in a php script and you have perhaps ommitted \"maildir:\" is there? The only other thing I can think of that pop is returning as null - in which case the whole finction evaluates to null (you never get just "maildir:" returned, but then I can't see why you would want to either...
	hosted by Lord

May 1st, 2006	Top \| #4
LordOfLA Godlike! Joined: February 2004 Location: Birmingham, UK Posts: 6,818 Blog Entries: 5 Reputation: 3651 Power: 179	Re: MySQL Arbitarary string prefix in select statement nah not php, configuration for dovecot imap/pop3 server For the moment I've manually specified a default location for maildir locations, but since that's stored in the database I'd like it to figure it out for itself However I need to prefix "maildir:" to the value in the database so that insted of /usr/local/mail/domain/user_part_of_full_mail_address/Maildir it gets maildir:/usr/local/mail/domain/user_part_of_full_mail_address/Maildir
	If HK-47 and GLaDOS had a child, the character they create would cause the video game world to overdose on awesome. -sheridanmovieguy: Dragon age forum user.

May 1st, 2006	Top \| #5
LordOfLA Godlike! Joined: February 2004 Location: Birmingham, UK Posts: 6,818 Blog Entries: 5 Reputation: 3651 Power: 179	Re: MySQL Arbitarary string prefix in select statement Well I don't know what I was doing wrong last night but the following works now: Code: SELECT CONCAT('maildir:',pop) AS mail, uid, gid FROM users WHERE username = %u" %u gets expanded by dovecot to be the full username as supplied at pop3/imap4 login and is a full email address. My guess is that I was supposed to use mail and not home. But works now
	If HK-47 and GLaDOS had a child, the character they create would cause the video game world to overdose on awesome. -sheridanmovieguy: Dragon age forum user.

Latest News Posts

Latest Forum Posts

Latest Member Blogs

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
C++: cant resolve string --> string[]	dubstar	Web Design & Coding	6	May 27th, 2007 8:10am
Simple Java for statement problem	Scott Young	Web Design & Coding	3	February 20th, 2006 11:07pm
Privacy Statement	Electronic Punk	News & Information	0	March 20th, 2004 2:27am
modem string	bluzeboy	Windows Desktop Systems	2	November 22nd, 2002 2:49pm
modem string	bluzeboy	Windows Desktop Systems	3	September 20th, 2002 2:08am