phpmyadmin Question posted in
The official documentation can be found here.

How to match any letter in SQL? – Phpmyadmin

I want to return rows where certain fields follow a particular pattern such as whether a particular character in a string is a letter or number. To test it out, I want to return fields where the first letter is any letter. I used this code. 

SELECT * FROM sales WHERE `customerfirstname` like '[a-z]%';

It returns nothing. So I would think that the criteria is the first character is a letter and then any following characters do not matter.
The following code works, but limits rows where the first character is an a.

SELECT * FROM sales WHERE `customerfirstname` like 'a%';

Am I not understanding pattern matching? Isn’t it [a-z] or [A-Z] or [0-9] for any letter or number?
Also if I wanted to run this test on the second character in a string, wouldn’t I use

SELECT * FROM `sales` WHERE `customerfirstname` like '_[a-z]%'

This is for SQL and MySQL. I am doing this in phpmyadmin.

Tags:

2

Answers


  1. 0

    You want to use regular expressions:

    SELECT s.* 
FROM sales s
WHERE s.customerfirstname REGEXP '^[a-zA-Z]';
    Login or Signup to reply.
  2. 0

    This can be achieved with a regular expression. 

    SELECT * FROM sales WHERE REGEXP_LIKE(customerfirstname, '^[[:alpha:]]');

    ^ denotes the start of the string, while the [:alpha] character class matches any alphabetic character.

    Just in case, here are a few others character classes that you may find useful :

    • alnum : dlphanumeric characters
    • digit: digit characters
    • lower : lowercase alphabetic characters
    • upper: uppercase alphabetic characters

    See the mysql regexp docs for many more…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search