Shantanu's Blog

I have a column named "path" containing web urls and I'd like to extract the file extension from those that have one (some are folder links) and place it

into a column named "ext" for quick sorting of the content and I was wondering how I'd construct a query to do that for me. In the future, I'll have my PHP

script do it as the urls are added but for now I have a quite a bit of legacy content to sort through.

Ans:

select url,
lower(
substring_index(
substring_index(
case
when locate('.',substring_index(url, '/', -1)) <> 0 then substring_index(url, '/', -1)
else '.'
end,
'.', -1),
'?', 1)
) from yourtable

Hope this helps :)

obviously, you need to convert it into an update statement,

http://forums.mysql.com/read.php?10,190785,190800#msg-190800

_____


I have to implement something which apparently looks pretty simple, but in fact it is not.

Let assume the table : MYTABLE with 2 columns : A and B

For example :

A B
10 20
12 14
12 20
15 20
10 30
12 30
18 40
15 25

I'd like to find values of B where A=10 and A=12 AND A=15 for the same value of B. In my example it would return only B=20.

Is there a simple way of doing this ? I tried using subqueries, but I failed.

Ans:

SELECT b FROM mytable
WHERE a=10 OR a=12 OR a = 15
GROUP BY b
HAVING COUNT(a) = 3
HAVING COUNT( DISTINCT a ) = 3;

http://forums.mysql.com/read.php?10,190709,190720#msg-190720

_____

I have been playing with this query for a while and can't get the results i want. can somone help me.

here is my Query:

SELECT (ps.round1+ps.handicap) AS rd1
FROM userchoice uc
JOIN players p
ON uc.playerId=p.playerId
JOIN playerscores ps
ON ps.playerId=uc.playerId AND ps.tournamentId=uc.tourneyId AND ps.round1!='0'
WHERE uc.username='billy' AND uc.entryId=1
ORDER BY rd1 ASC
LIMIT 4

Results:
Rd1
60
65
68
69

Now, how can i modify this query so that it adds those 4 numbers together and returns the total from the four (which in this case would be 262)

I tried throwing a SUM(ps.round1+ps.handicap) AS rd1 in there, but that gave me a value of 497.

Ans:

SELECT SUM(rd1)
FROM (
SELECT ps.round1+ps.handicap AS rd1
FROM userchoice uc
JOIN players p
ON uc.playerId=p.playerId
JOIN playerscores ps ON ps.playerId=uc.playerId AND ps.tournamentId=uc.tourneyId AND ps.round1!='0'
WHERE uc.username='billy' AND uc.entryId=1
ORDER BY rd1 ASC LIMIT 4
) AS tmp;


http://forums.mysql.com/read.php?10,190447,190458#msg-190458

_____

Can someone please help me with an sql query to filter duplicated rows in a table. The problem is that sometimes row1 and row2 fields are reversed but this is something that i would like removed from the table.

name1 | name2
-----------------
John | Jane
Fred | Claudia
Jim | Claire
Jane | John
Claire | Jim

So if rows where name1/name2=name2/name1 then one of these rows should be filtered. i.e if John/Jane appears on one row then Jane/john should be removed from the table in the other row.

Sounds easy but i cannot find a way to do it, any ideas gurus out there?

Ans:

DELETE n1.* FROM names AS n1,names AS n2 WHERE n1.name1 = n2.name2 AND n2.name1 = n1.name2;


http://forums.mysql.com/read.php?10,190338,190345#msg-190345

_____

Other notable threads are as follows:
1) MySQL interview questions...

http://forums.mysql.com/read.php?10,191044,191044#msg-191044

2) Encrypted passwords
http://forums.mysql.com/read.php?10,190993,190993#msg-190993

3) Full Text Search
http://forums.mysql.com/read.php?10,190979,190979#msg-190979

http://forums.mysql.com/read.php?10,193470,193470#msg-193470

4) Subquery Optimization

http://forums.mysql.com/read.php?10,192870,192891#msg-192891

http://forums.mysql.com/read.php?10,192503,192511#msg-192511

Labels: mysql case study