Multiply an Unsigned Int by 1 on a MySQL Select

Rama Giri
Updated on 30-Jun-2020 12:47:59

129 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> Value int    -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------+ | Value | +-------+ |    10 | | ... Read More

Order By Number of Characters in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 12:46:58

481 Views

To order by number of chars, use ORDER BY and LENGTH() method. Following is the syntax −select *from yourTableName order by LENGTH(yourColumnName) DESC;Let us first create a table −mysql− create table DemoTable    -> (    -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable ... Read More

Concatenate MySQL DISTINCT Query Results into a String

Rama Giri
Updated on 30-Jun-2020 12:45:56

1K+ Views

Use group_concat() function from MySQL to concatenate. Let us first create a table −mysql> create table DemoTable    -> (    -> Subject varchar(10)    -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('C++'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('C++'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('MySQL'); Query ... Read More

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

491 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

409 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

673 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

158 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

166 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

Advertisements