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
-
Economics & Finance
Articles by Sharon Christine
Page 14 of 34
How to set a string with hyphen and numbers in MySQL varchar?
To set a string with hyphen and numbers, you need to use single quotes. For example, 'Customer-1234-899', 'Customer-9383-901', etc.Let us first create a table −mysql> create table DemoTable -> ( -> CustomerId varchar(100) -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Customer-1234-899'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Customer-8373-900'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Customer-9383-901'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTableOutputThis ...
Read MoreExtract the middle part of column values in MySQL surrounded with hyphens and display in a new column?
Use the SUBSTR() method to extract the middle part of column values surrounded with hyphens, for example, “11-84848-11”.Let us first create a table −mysql> create table DemoTable -> ( -> Number varchar(100), -> Number1 varchar(100) -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Number) values('11-84848-11'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Number) values('22-99999-22'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------+---------+ | Number ...
Read MoreDiscard last 3 characters of a field in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-090'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('STU-123'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('STU-678'); Query OK, 1 row affected (0.29 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-----------+ | StudentId | +-----------+ | STU-090 | ...
Read MoreComparing two columns in a single MySQL query to get one row?
For this, you can use ORDER BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> Num1 int, -> Num2 int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(60, 249); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(59, 250); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------+------+ | Num1 | Num2 | +------+------+ | 60 | 249 ...
Read MoreSearching for an integer value in a varchar field in MySQL?
To search for an integer value in a varchar filed, you can use CASE statement.Let us first create a table. Consider, we have a list of email-ids −mysql> create table DemoTable -> ( -> Title varchar(100) -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('9John@example.com'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('3Carol@gmail.com'); Query OK, 1 row affected (0.45 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------------+ | Title ...
Read MoreMySQL query to create table dynamically?
For this, you can use stored procedure. Let us create a table dynamically with two columns i.e. StudentId as int, whereas StudentName as varchar −mysql> DELIMITER $$ mysql> CREATE PROCEDURE creatingDynamicTableDemo(yourTableName VARCHAR(200)) -> BEGIN -> SET @name = yourTableName; -> SET @st = CONCAT(' '> CREATE TABLE IF NOT EXISTS `' , @name, '` ( '> `StudentId` int UNSIGNED NOT NULL AUTO_INCREMENT, '> `StudentName` varchar(20) NOT NULL, '> PRIMARY KEY (`StudentId`) '> ) '> '); -> PREPARE ...
Read MoreMySQL query with two boolean conditions to extract date based on hour?
Let us first create a table −mysql> create table DemoTable -> ( -> AdmissionDate datetime -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-10 10:45:10'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values('2019-02-12 20:50:00'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('2019-02-12 16:10:19'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------------+ | AdmissionDate ...
Read MoreHow do I select 5 random rows from the 20 most recent rows in MySQL?
For random, use RAND() method. And for limit on rows, use the LIMIT() method.Let us first create a table −mysql> create table DemoTable -> ( -> ShippingDate datetime -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-01'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('2019-01-03'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('2019-01-05'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-01-07'); Query OK, 1 row affected (0.11 sec) ...
Read MoreHow to get everything before the last occurrence of a character in MySQL?
You can use below syntax. Following is the syntax −update yourTableName set yourColumnName=REVERSE(SUBSTRING(REVERSE(yourColumnName), INSTR(REVERSE(yourColumnName), '.')));Let us first create a table −mysql> create table DemoTable -> ( -> Words text -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Value1. Value2 .Value3.Value4.Value5'); 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 −+--------------------------------------+ | Words ...
Read MoreHow to update data in a MySQL database without removing the old data?
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