Get Only Digits Using REGEXP in MySQL

Chandu yadav
Updated on 30-Jun-2020 12:45:51

21K+ Views

If you want to get only digits using REGEXP, use the following regular expression( ^[0-9]*$) in where clause.Case 1 − If you want only those rows which have exactly 10 digits and all must be only digit, use the below regular expression.SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[0-9]{10}$';Case 2 − If you want only those rows with the digit either 1 or more, the following is the syntax −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[0-9]*$';The above syntax will give only those rows that do not have any any characters.To understand the above syntax, let us create a table. The query ... Read More

MySQL Query to Search Between Comma Separated Values Within One Field

karthikeya Boyini
Updated on 30-Jun-2020 12:45:13

482 Views

Use FIND_IN_SET for this. Let us first create a table −mysql> create table DemoTable -> ( -> ListOfValues text -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10|20|30|40|50|60|100'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-----------------------+ | ListOfValues | +-----------------------+ | 10|20|30|40|50|60|100 | +-----------------------+ 1 row in set (0.00 sec)Following is the query to search between comma separated values within one ... Read More

Subquery to Exclude a Particular Row in MySQL

Kumar Varma
Updated on 30-Jun-2020 12:42:49

403 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Age) values('John', 21); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Name, Age) values('Carol', 22); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Name, Age) values('David', 23); Query OK, 1 row affected (0.54 sec)Display all records from the table using select statement ... Read More

Fetch Rows Where First Character is Not Alphanumeric in MySQL

George John
Updated on 30-Jun-2020 12:42:48

664 Views

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 ... Read More

Use ADD and CHANGE with ALTER Statement in MySQL

Rama Giri
Updated on 30-Jun-2020 12:41:40

147 Views

Yes, we can use ADD and CHANGE with ALTER statement. Let us first create a table −mysql> create table DemoTable    -> (    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.84 sec)Now check the description of table.mysql> desc DemoTable;OutputThis will produce the following output −+-------+--------------+------+-----+---------+-------+ | Field | Type         | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | Name  | varchar(100) | YES  |   | NULL |   | | Age   | int(11)   ... Read More

Update Data in MySQL Database Without Removing Old Data

Sharon Christine
Updated on 30-Jun-2020 12:39:56

1K+ Views

For this, you can use UPDATE and concatenate the new data with the old one to save the old data as well −update yourTableName set yourColumnName=concat(yourColumnName, ", yourValue");Let us first create a table −mysql> create table DemoTable -> ( -> CustomerName varchar(100) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.14 sec)Display all records from ... Read More

Get Part of a String Based on a Character in MySQL

Kumar Varma
Updated on 30-Jun-2020 12:39:04

162 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> Code varchar(100)    -> ); Query OK, 0 rows affected (1.07 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('/101/102/106'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/110/111/101'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/111/114/201'); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable values('/111/118'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce ... Read More

Open MySQL Command Line on Windows 10

Ankith Reddy
Updated on 30-Jun-2020 12:38:34

24K+ Views

To open the MySQL command line from cmd, you need to use username root with your password.Follow the below given steps. The syntax is as follows −cd \> press enter key cd Program Files\MySQL\MySQL Server 8.0\bin> press enter key C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p press enter key Enter password: ******Here is the step by step instruction to open MySQL command line. First, Go to START > RUN or Open Run using Windows+R command −Type CMD and hit OK button −After pressing OK button, the CMD will open −Now you need to follow the above instruction. First reach your bin ... Read More

Return Entire Date and Time Based on a String in MySQL

Sharon Christine
Updated on 30-Jun-2020 12:36:03

124 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> AdmissionDate varchar(100)    -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Wed, 19 Jun 2019 04:10:20'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------------------+ | AdmissionDate | +---------------------------+ | Wed, 19 Jun 2019 04:10:20 | +---------------------------+ 1 row in set (0.00 sec)Following is ... Read More

Select Fixed Number of Random Records from a MySQL Table

Sharon Christine
Updated on 30-Jun-2020 12:35:14

198 Views

For random records, you can use rand() method. To set the number of records, use the LIMIT −select *from yourTableName order by rand() limit numberOfRecords;Let us first create a table −mysql> create table DemoTable    -> (    -> LastName varchar(100)    -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Brown'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Smith'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Taylor'); Query OK, 1 row affected (0.10 sec) mysql> insert ... Read More

Advertisements