Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mysql select concat selection

Posted on 2009-05-01
19
Medium Priority
?
1,044 Views
Last Modified: 2013-12-12
Hello:
I have already posted my question at:
http://www.experts-exchange.com/Database/MySQL/Q_24340456.html
I thought I found the solution there but there is a problem:

solution (that does not woirk correctly)
SELECT CONCAT('pix1/', LEFT(username,1),'/',LEFT(username,2), '/', username , '-1.jpg')

It makes a kind of recurrent selection that make the result growing all the rime !

here is my request again


I have in my database users, fields name username who contains for instance the value dialogoo
I need to write a simple select function that selects:
pix1/d/di/dialogoo-1.jpg

pix1 is FIXED
d is the first letter
di are the first 2 letters
-1.jpg is fixed

so it it something like
SELECT "pix1/"+{firstLetter(username)+"/"+{first2Letters(username)}+"/"+{username}+"-1.jpg"
Any clue ?


regards
0
Comment
Question by:yarekGmail
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24280951
SELECT CONCAT('pix1/', LEFT(username,1),'/',LEFT(username,2), '/', TRIM(username) , '-1.jpg')
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24281599
The syntax looks correct how you had it; therefore, as racek is suggesting, check if there are extra spaces.  If there are, you will probably want to do TRIM on every occurrence of the username to ensure you don't have spaces in front since building directories out of first 1-2 characters.
0
 
LVL 14

Expert Comment

by:racek
ID: 24281923
maybe

SELECT CONCAT(
'pix1/',
LEFT(TRIM(username,1)),'/',
LEFT(TRIM(username,2)), '/',
TRIM(username) , '-1.jpg')
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:yarekGmail
ID: 24282733
the original table has these values:
bel01
CcC01
CcC02
CcF01
CcF02
CcH01
...

here is the result I got:
pix1/b/be/bel01-1.jpg
pix1/b/C/Cc/CcC01-1.jpg
pix1/b/C/Cc/C/Cc/CcC02-1.jpg
pix1/b/C/Cc/C/Cc/CcC02C/Cc/CcF01-1.jpg
pix1/b/C/Cc/C/Cc/CcC02C/Cc/CcF01-1.jpgC/Cc/CcF02-1...
etc...

You see ? It keeps on growing !

The result keeps on GROWING ! ! !
0
 
LVL 14

Expert Comment

by:racek
ID: 24282840
show me the whole query ...
0
 

Author Comment

by:yarekGmail
ID: 24283106
SELECT CONCAT('pix1/', LEFT(username,1),'/',LEFT(username,2), '/', TRIM(username) , '-1.jpg')  from inscrits

That's all !
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24283439
What do you mean by keep on growing.

Whats the result are you getting and what is your expected result?
I am getting correct result for the query suggested here.

mysql> create table inscrits(username varchar(1000));
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into inscrits values('bel01');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into inscrits values('CcC01');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into inscrits values('CcC02');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into inscrits values('CcF01');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into inscrits values('CcF02');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into inscrits values('CcH01');
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> SELECT username,CONCAT('pix1/', LEFT(username,1),'/',LEFT(username,2), '/
', TRIM(username) , '-1.jpg') as complete_username
    ->   from inscrits;
+----------+-----------------------+
| username | complete_username     |
+----------+-----------------------+
| bel01    | pix1/b/be/bel01-1.jpg |
| CcC01    | pix1/C/Cc/CcC01-1.jpg |
| CcC02    | pix1/C/Cc/CcC02-1.jpg |
| CcF01    | pix1/C/Cc/CcF01-1.jpg |
| CcF02    | pix1/C/Cc/CcF02-1.jpg |
| CcH01    | pix1/C/Cc/CcH01-1.jpg |
+----------+-----------------------+
6 rows in set (0.00 sec)

Open in new window

0
 

Author Comment

by:yarekGmail
ID: 24285322
WHEN I run step by step your SQLs (in a new database),
create table inscrits(username varchar(1000));
insert into inscrits values('bel01');
etc...

I GOT SOMETHING DIFFERENT ! ! !
 
username  complete_username  
bel01 pix1/b/be/bel01-1.jpg
CcC01 pix1/b/C/Cc/CcC01-1.jpg
CcC02 pix1/b/C/Cc/C/Cc/CcC02-1.jpg
CcF01 pix1/b/C/Cc/C/Cc/CcC02C/Cc/CcF01-1.jpg
CcF02 pix1/b/C/Cc/C/Cc/CcC02C/Cc/CcF01-1.jpgC/Cc/CcF02-1...
CcH01 pix1/b/C/Cc/C/Cc/CcC02C/Cc/CcF01-1.jpgC/Cc/CcF02-1...
Here is the screenshot ! ! ! !


0
 

Author Comment

by:yarekGmail
ID: 24285327
here is the screenshot !
resultSQL.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24285334
What is you MySQL version? I don't understand what is the problem. Can you try concatinating one string at a time and see the result.
0
 

Author Comment

by:yarekGmail
ID: 24285634
mysql 3.23.49

resultSQL2.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24287130
can you try this?
SELECT username,CONCAT('pix1/', LEFT(username,1),'/',LEFT(username,2), '/', TRIM(username) , '-1.jpg') as complete_username
  FROM inscrits
 GROUP BY username;

Open in new window

0
 

Author Comment

by:yarekGmail
ID: 24287484
Not better !
resultSQL2.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24287831
its a simple CONCAT and not sure why its functioning like that. Can you run this and chek whats the output?
SELECT username,CONCAT(username) as complete_username
  FROM inscrits;

Open in new window

0
 

Author Comment

by:yarekGmail
ID: 24289007
SELECT username,CONCAT(username) as complete_username   FROM inscrits;

it works normally !
resultSQL2.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24289031
please try all combinations and check the resutl.
SELECT username,CONCAT('pix1/',username) as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1)) as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1),'/') as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1),'/',LEFT(username,2)) as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1),'/',LEFT(username,2),'/') as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1),'/',LEFT(username,2),'/',TRIM(username)) as complete_username FROM inscrits;
SELECT username,CONCAT('pix1/',LEFT(username,1),'/',LEFT(username,2),'/',TRIM(username),'-1.jpg') as complete_username FROM inscrits;

Open in new window

0
 

Author Comment

by:yarekGmail
ID: 24289692
1) SELECT pseudo,CONCAT('pix1/',pseudo) as complete_pseudo FROM inscrits
OK

2) SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1)) as complete_pseudo FROM inscrits;
NOT OK

3) SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1),'/') as complete_pseudo FROM inscrits;
NOT OK

4) SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1),'/',LEFT(pseudo,2)) as complete_pseudo FROM inscrits;
NOT OK

5) SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1),'/',LEFT(pseudo,2),'/') as complete_pseudo FROM inscrits;
NOT OK

6) SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1),'/',LEFT(pseudo,2),'/',TRIM(pseudo)) as complete_pseudo FROM inscrits;

NOT OK
SELECT pseudo,CONCAT('pix1/',LEFT(pseudo,1),'/',LEFT(pseudo,2),'/',TRIM(pseudo),'-1.jpg') as complete_pseudo FROM inscrits


Infact only 1) works normally

Then other results keeps on growing more and more !
I have re-installed mySQL  3.23.49
0
 
LVL 14

Accepted Solution

by:
psadac earned 1500 total points
ID: 24312653
"I have re-installed mySQL  3.23.49"
is there a good reason to stay with this very old version ?
there must be a bug with LEFT() with this version, try to replace it with SUBSTRING()
SELECT pseudo,CONCAT('pix1/',SUBSTRING(pseudo,1,1),'/',SUBSTRING(pseudo,1,2),'/',TRIM(pseudo),'-1.jpg') as complete_pseudo FROM inscrits

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question