Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
456 Views
