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)