Fetch rows where first character is not alphanumeric in MySQL?

To fetch rows where first character is not alphanumeric, you can use the following regular expression.

Case 1 − If you want those rows that starts from a digit, you can use the following syntax −

SELECT *FROM yourTableName
WHERE yourColumnName REGEXP '^[0-9]';

Case 2 − If you want those rows that start from an alphanumeric, use the following syntax −

SELECT *FROM yourTableName
WHERE yourColumnName REGEXP '^[^0-9A-Za-z]' ;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table getRowsFirstNotAlphanumeric
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> UserPassword varchar(20),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('@123456');
Query OK, 1 row affected (0.19 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('#7666666');
Query OK, 1 row affected (0.22 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('98876Carol');
Query OK, 1 row affected (0.16 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('$12345Carol');
Query OK, 1 row affected (0.09 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('%David567');
Query OK, 1 row affected (0.10 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('123456Larry');
Query OK, 1 row affected (0.07 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('909Robert');
Query OK, 1 row affected (0.12 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('3333Maxwell');
Query OK, 1 row affected (0.09 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('_123456Bob');
Query OK, 1 row affected (0.10 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('5767676Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into getRowsFirstNotAlphanumeric(UserPassword) values('(88883Mike');
Query OK, 1 row affected (0.11 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from getRowsFirstNotAlphanumeric;

The following is the output −

+----+--------------+
| Id | UserPassword |
+----+--------------+
| 1 | @123456       |
| 2 | #7666666      |
| 3 | 98876Carol    |
| 4 | $12345Carol   |
| 5 | %David567     |
| 6 | 123456Larry   |
| 7 | 909Robert     |
| 8 | 3333Maxwell   |
| 9 | _123456Bob    |
| 10 | 5767676Chris |
| 11 | (88883Mike   |
+----+--------------+
11 rows in set (0.00 sec)

Case 1 −Here is the query to get all rows which does not start from alphanumeric −

mysql> SELECT *FROM getRowsFirstNotAlphanumeric
-> WHERE UserPassword REGEXP '^[0-9]';

The following is the output −

+----+--------------+
| Id | UserPassword |
+----+--------------+
| 3 | 98876Carol    |
| 6 | 123456Larry   |
| 7 | 909Robert     |
| 8 | 3333Maxwell   |
| 10 | 5767676Chris |
+----+--------------+
5 rows in set (0.00 sec)

Case 2: Here is the query to get all rows which starts from alphanumeric:

mysql> SELECT *FROM getRowsFirstNotAlphanumeric
-> WHERE UserPassword REGEXP '^[^0-9A-Za-z]';

The following is the output:

+----+--------------+
| Id | UserPassword |
+----+--------------+
| 1 | @123456       |
| 2 | #7666666      |
| 4 | $12345Carol   |
| 5 | %David567     |
| 9 | _123456Bob    |
| 11 | (88883Mike   |
+----+--------------+
6 rows in set (0.00 sec)