Jump to content

Random Beginning Letter in MySQL


thunderousity

Recommended Posts

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

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

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 by thunderousity
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...