thunderousity Posted September 5, 2016 Share Posted September 5, 2016 I have been working on a query to return a list of names filtered by a random beginning letter A - Z. The following SELECT gives me a random letter using ASCII characters A-Z or 65-90. 1. SELECT floor(65 + (rand() * (1+90-65))) as RandomBetween65and90 The next statement is my main query which at the moment filters rows using Like by a specified ASCII Decimal. In the example below this is 65 or A I had to CAST the CHAR function to return an ASCII Character rather than a decimal for the Like 'A%' 2. SELECT n.Name, g.Gender FROM tblnames AS n INNER JOIN tblgender AS g ON n.GenderID = g.GenderID WHERE name Like CONCAT((SELECT CAST(CHAR(65) AS CHAR(3))), '%') These both work in isolation, however when I try to combine the 2 statements and replace the 65 in the 2nd statement with the 1st statement as a subquery to give me a random letter I don't get the results I expect. I seem to get random rows rather than rows based on a random beginning letter. SELECT n.Name, g.Gender FROM tblnames AS n INNER JOIN tblgender AS g ON n.GenderID = g.GenderID WHERE name Like CONCAT((SELECT CAST(CHAR((SELECT floor(65 + (rand() * (1+90-65))))) AS CHAR(3))), '%') ORDER BY n.Gender Link to comment Share on other sites More sharing options...
Ingolme Posted September 5, 2016 Share Posted September 5, 2016 You don't need the SELECT statements in there, just use the expressions. You have way too many parentheses in your code. This will probably work: CONCAT( CAST( CHAR( floor( 65 + rand() * (1+90-65) ) ) AS CHAR(3) ) , '%') Link to comment Share on other sites More sharing options...
thunderousity Posted September 8, 2016 Author Share Posted September 8, 2016 (edited) This looks much neater thanks. However, this seems to randomise the name order but the names always start with an A SELECT n.Name, g.Gender FROM tblnames AS n INNER JOIN tblgender AS g ON n.GenderID = g.GenderID WHERE name Like CONCAT( CAST( CHAR( floor( 65 + rand() * (1+90-65) ) ) AS CHAR(3) ) , '%') Edited September 8, 2016 by thunderousity Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now