Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Articles by Rama Giri
Page 7 of 9
MySQL query to update string field by concatenating to it?
For concatenating a string field, use CONCAT() function. Let us first create a table −mysql> create table DemoTable -> ( -> SequenceId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentId) values('STU'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentId) values('STU1'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+------------+-----------+ | SequenceId | StudentId | +------------+-----------+ | 1 ...
Read MoreCan we replace a number with a String in a MySQL result set?
Yes, we can do that using the CASE statement. Let us first create a table −mysql> create table DemoTable -> ( -> isMarried boolean -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.19 sec)Display all records from the table ...
Read MoreIs there PHP basename() equivalent in MySQL?
If given a string containing a path to a file, the PHP basename() function will return the base name of the file. To get its equivalent in MySQL, you can use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable -> ( -> Location varchar(200) -> ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('C:\Web\Sum.java'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('E:\WebDevelopment\Image1.png'); Query OK, 1 row affected (0.42 sec)Display all records from the table using select statement ...
Read MoreConcatenating two strings in MySQL with space?
For this, you can use concat() function from MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> Subject varchar(100) -> ); Query OK, 0 rows affected (17.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Subject) values('John', 'MySQL'); Query OK, 1 row affected (1.19 sec) mysql> insert into DemoTable(Name, Subject) values('Chris', 'SQL Server'); Query OK, 1 row affected (0.88 sec) mysql> insert into DemoTable(Name, Subject) values('Robert', 'MongoDB'); Query OK, 1 row affected ...
Read MoreHow to return rows that have the same column values in MySQL?
Use GROUP BY clause for this. Let us first create a table −mysql> create table DemoTable -> ( -> StudentId int, -> StudentMarks int -> ); Query OK, 0 rows affected (4.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(23, 58); Query OK, 1 row affected (0.70 sec) mysql> insert into DemoTable values(25, 89); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values(26, 58); Query OK, 1 row affected (1.13 sec) mysql> insert into DemoTable values(28, 98); Query OK, 1 row affected (0.86 sec)Display ...
Read MoreMySQL query to get only the minutes from datetime?
Let us first create a table −mysql> create table DemoTable -> ( -> ShippingDate datetime -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-10 10:04:45'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2019-06-11 05:45:00'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('2019-06-12 07:00:55'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+---------------------+ | ShippingDate | +---------------------+ | 2019-01-10 10:04:45 | | 2019-06-11 05:45:00 | | 2019-06-12 07:00:55 | +---------------------+ 3 rows in set (0.00 sec)Here is the query to get minutes in MySQL −mysql> select minute(ShippingDate) as Minutes from DemoTable;Output+---------+ | Minutes | +---------+ | 4 ...
Read MoreConcatenate date and time from separate columns into a single column in MySQL
For this, concatenate both the date and time using CONCAT() function. Let us first create a table −mysql> create table DemoTable -> ( -> ShippingDate date, -> ShippingTime time, -> ShippingDatetime datetime -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ShippingDate, ShippingTime) values('2019-01-10', '10:40:20'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ShippingDate, ShippingTime) values('2019-06-14', '04:00:10'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+--------------+--------------+------------------+ | ShippingDate | ShippingTime | ...
Read MoreDisplay all records ignoring the current date record in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100), -> DueDate datetime -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command. Let’s say the current date is “2019-07-05” −mysql> insert into DemoTable values('Chris', '2019-06-24'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Chris', '2018-01-01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Robert', '2019-07-05'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('Carol', '2019-08-03'); Query OK, 1 row affected (0.22 ...
Read MoreSet numbers as a varchar field and compare in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentScore varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentScore) values('90'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(StudentScore) values('100'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentScore) values('56'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(StudentScore) values('98'); Query OK, 1 row affected (0.13 sec)Display all records from the table ...
Read MoreMySQL query to replace a string after the last / in a column with directory links?
For this, use the substring_index() method. Let us first create a table −mysql> create table DemoTable -> ( -> FolderName varchar(100), -> FolderLocation varchar(200) -> ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('CProgram', 'C:/AllPrograms/.....'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Images', 'E:/MyImage/home/garbage'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+------------+-------------------------+ | FolderName | FolderLocation | +------------+-------------------------+ | CProgram | C:/AllPrograms/..... ...
Read More