FIND_IN_SET Function - How to find position of string in Comma separate values list

FIND_IN_SET Function - How to find position of string in Comma separate values list

FIND_IN_SET function is used to find the position of string in list of comma separated values. FIND_IN_SET can return depending upon the searched value and values present in comma separated list.

Syntax:


FIND_IN_SET( StringtoFind,Comma_Separated_String_List);



  1. Either StringToFind or Comma_Separated_String_List is null, then output is going to be null for find_in_set.
  2. Find_IN_Set will return zero if the StringtoFind is not present in Comma_Separated_String_List.
  3. If StringtoFind is present in Comma_Separated_String_List then positive number will be returned.
Below is example that covers all above scenarios or result set returned by find_in_set.

Select find_in_set('aamir',null),
find_in_set(null,'aamir,shahzad'),
find_in_set('Robert','aamir,shahzad'),
find_in_set('robert','aamir,shahzad,robert,test,test2');


How to use Find_in_set in MySQL to find the position in Comma Delimited value list

How to find line break and carriage return (\r\n) in MySQL - MySQL Tutorial

How to find line break and carriage return (\r\n) in MySQL

You are working as MySQL Developer and you need to write a query that should return all the values from column which has carriage return or line break or both of them.

Let's create sample test table with some sample data and then write the query to get the records with line break and carriage return (\r\n).


create table test(id int, name varchar(100));

-- insert some sample data
insert into test 
values(1,'Aamir\n'),
(2,'shahzad'),
(3,'Robert\r\n'),
(4, 'This is \n test'); 



Below query will return all the values which has \n or \r or both of them.

select * from test;
SELECT * FROM test WHERE
name like '%\n%'
or name like '%\r\n%';


How to find records with line break and carriage return in MySQL - MySQL Tutorial

TRIM Function - How to use TRIM Function in MySQL

TRIM Function - How to use TRIM Function in MySQL

Trim function in MySQL is used to remove unwanted characters from starting of ending of string. There are different ways to remove the unwanted characters by using Trim. You might only want to remove leading or trailing or both.


TRIM('String Value');
LTRIM('String Value');
RTRIM('String Value')


TRIM function above will remove the leading and trailing spaces from String value. LTrim will only remove blank spaces from starting and RTRIM will remove trailing blank spaces from String value.


Select ' TechBrothersIT ',
trim(' TechBrothersIT '),
ltrim(' TechBrothersIT '),
rtrim(' TechBrothersIT ');


How to use Trim Function in MySQL - MySQL Tutorial

There is another way to write the TRIM syntax, if you want to also remove specific characters from trailing or leading you can do that.

Syntax:


TRIM([{BOTH|LEADING|TRAILING} [string you want to remove]] FROM str);


In below example, we are removing starting and trailing spaces and also in last part, we are removing Trailing IT from string.


Select ' TechBrothersIT ',
trim(both from ' TechBrothersIT '),
TRIM(Leading from ' TechBrothersIT '),
TRIM(Trailing from ' TechBrothersIT '),
TRIM(Trailing 'IT' from ' TechBrothersIT');


How to use TRIM Function in MySQL to remove Leading or Trailing Values from String

SUBSTRING Function - How to use SUBSTRING Function in MySQL

SUBSTRING Function - How to use SUBSTRING Function in MySQL


SUBSTRING Function in MySQL is used to extract a substring from a position with specific position.

Syntax:


SUBSTRING(string,position);
SUBSTRING(string,position,length);



So in below example if we want to start from 3rd characters and returned the rest of rows we can do that. In second part of select we are specificity telling start from 4th characters but only returned 4 records from 4th position.


Select substring('My Test string',3),
substring('My Test string',4,4) from customer;


How to use SUBSTRING Function in MySQL - MySQL Tutorial

REPLACE Function - How to find and replace string in value in MySQL

REPLACE Function - How to find and replace string in value in MySQL


REPLACE Function in MySQL can be used to find some string and then replace with new string value you like.

Syntax:


REPLACE(ColumnNameOrValue,old_string,New_string)


Let's create sample table customer and insert some data into it.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Some of the value in firstname has "1" , let's replace that with blank space. Also in phonenumber we have "-" and we would like to replace with ".".

Select firstname,
replace(firstname,'1','') as fname,
phonenumber,replace(phonenumber,'-','.') as pnumber from customer;

How to use Replace function in MySQL - MySQL Developer Tutorial

LEFT Function - How to get Some Characters from LEFT from String in MySQL

LEFT Function - How to get Some Characters from LEFT from String in MySQL

LEFT Function in MySQL is used to get left part of string with specified length.


LEFT('Value',Length)


Let's create sample table customer with sample data and then we will use Left function on different columns to get only left characters according to the length specified.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


Let's get area code those are first 3 characters in phone number and also get Initial Character(First Lettter) from FirstName column.


Select phonenumber,left(phonenumber,3),
firstname,left(firstname,1) from customer;


How to use LEFT Function in MySQL - MySQL Developer Tutorial

Char_length Function - How to find number of characters in string in MySQL

Char_length Function - How to find number of characters in string in MySQL

Char_Length is the function in MySQL that can be used to find the number of characters .

Let's create sample table customer and insert some sample data.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert some sample records
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


Let's use Char_Length to get character count for values in different columns.


Select firstname,char_length(firstname),lastname,
char_length(lastname),phonenumber,char_length(phonenumber) from customer;


Length Function - How to get Data of String in Bytes in MySQL

Length Function - How to get Data of String in Bytes in MySQL

Length function is used to return the size of string in Bytes in MySQL. It is equivalent to DataLength( ) Function in SQL Server.

Syntax: 


Select Length('StringValue');



Let's say we have customer table with some records and we would like to get the data length for firstname and lastname values.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;
-- Insert sample records
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


use Length( ) function to get the size of column values in Bytes.

Select firstname,length(firstname), lastname,length(lastname),phonenumber,length(phonenumber) from customer;


How to get size of Column in MySQL by using Length ( ) Function - MyQL Tutorial

CONCAT Function - How to Concatenate multiple rows into single filed in MySQL

CONCAT Function - How to Concatenate multiple rows into single filed in MySQL


Concat function in MySQL is used to concatenate string values. Concat function really helps when you need to row values in table.

Syntax:


Concat('firstvalue','secondvalue',.....)


Example :
Let's say if we want to concat two values "aamir" and shahzad" and have space between them. we can use below example.

Select concat('aamir',' ','shahzad') as MyColumn;


How to use Concat function in MySQL or MariaDB
Let's create sample table customer and insert some rows and then we will use Concat to concatenate firstname and lastname column.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Now let's use CONCAT function in MySQL to concatenate firstname and lastname.

Select *, concat(firstname,' ',ifnull(lastname,'')) as fullname from customer;


I have added ' ' between firstname and lastname, you can add any string in Concat function. I used the space so the full name is more readable.

Use Concat function to concatenate two columns in MySQL Table - MySQL developer Tutorial

Notice that the full name in first row is Null, that is because the lastname in first row is Null. If you will Concat Null with other values, the output is going to be Null. 

To handle Null values, you can use IFNull to replace null with blank space so you don't get the Null output, As shown below.


Select *, concat(IfNull(firstname,''),' ',IfNull(lastname,'')) 
as fullname from customer;


How to use IFNull in Concat function in MySQL to replace null with blank space - MySQL Tutorial

How to create AFTER UPDATE Trigger in MySQL 2nd Method - MySQL Developer Tutorial

How to create AFTER UPDATE trigger in MySQL

You are working as MySQL Developer and you are asked to create a trigger that should keep records of updated records. You are required to keep the old records and after update. To keep the records of updated records, you can use After Update Trigger in MySQL. In below example, we are going to create customer Audit table
Method  2: 
You can have only one set of columns and then an extra column that tells about Record type such as "OLD" or "NEW".
Method 1: 
Create audit table with list of same columns but name them old_column1,old_column2....,new_column1,new_column2....


Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
  ,RecordType varchar(100)
  ,actiondate datetime
  ,actionby varchar(100)
) ;




Now let's create After Update Trigger. From here you can get the idea how your After Insert Trigger will look like.

Create AFTER UPDATE Trigger in MySQL:



-- drop trigger customer_after_update;
DELIMITER //

CREATE TRIGGER customer_after_update
AFTER update
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);
  DECLARE var_date datetime;


   -- Get user name who is performing update
   SELECT USER() INTO var_User;
    SELECT SYSDATE() into var_date;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
       VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,var_date,
     var_User,"oldrecord"
   );
   
   insert into customer_audit(idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
   VALUES(
   new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender, var_date, var_User,"updatedRecord" );
    

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is updating the record. We used SYSDATE() function to record the datetime for update.

Now you can update a record in customer table and then check the customer_audit table if all changes are audited successfully.

How to create After Update Trigger in MySQL Method 1 - MySQL Developer Tutorial

How to create AFTER UPDATE trigger in MySQL

You are working as MySQL Developer and you are asked to create a trigger that should keep records of updated records. You are required to keep the old records and after update. To keep the records of updated records, you can use After Update Trigger in MySQL. In below example, we are going to create customer Audit table which will have old columns and new columns, (I am calling this Method1) You can have only one set of columns and then an extra column that tells about Record type such as "OLD" or "NEW", (I am calling this Method2) .  For this example , I have decided to duplicate the columns.


Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `old_idcustomer` int, 
  `old_firstname` varchar(50)  NULL,
  `old_lastname` varchar(30)  NULL,
  `old_age` int(11) DEFAULT NULL,
  `old_phonenumber` char(11) DEFAULT NULL,
  `old_dob` date DEFAULT NULL,
  `old_gender` char(1) NOT NULL,
  `new_idcustomer` int, 
  `new_firstname` varchar(50)  NULL,
  `new_lastname` varchar(30)  NULL,
  `new_age` int(11) DEFAULT NULL,
  `new_phonenumber` char(11) DEFAULT NULL,
  `new_dob` date DEFAULT NULL,
  `new_gender` char(1) NOT NULL
  ,actiondate datetime
  ,actionby varchar(100)
) ;




Now let's create After Update Trigger. From here you can get the idea how your After Insert Trigger will look like.

Create AFTER UPDATE Trigger in MySQL:



DELIMITER //

CREATE TRIGGER customer_after_update
AFTER update
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);

   -- Get user name who is performing update
   SELECT USER() INTO var_User;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( old_idcustomer,old_firstname,old_lastname,old_age,old_phonenumber,
   old_dob,old_gender,
   new_idcustomer,new_firstname,new_lastname,new_age,new_phonenumber,
   new_dob,new_gender,
   actiondate,actionby)
    
   VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,
   new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is updating the record. We used SYSDATE() function to record the datetime for update.

Now you can update a record in customer table and then check the customer_audit table if all changes are audited successfully.

How to create after insert trigger in MySQL - MySQL Developer Tutorial

How to create AFTER INSERT trigger in MySQL

Often the companies want to keep the records who has inserted the records in the table. It helps for audit purpose even later the records are deleted from actual table. In MySQL we can create After Insert trigger that can be used to insert the same record which we are inserting in actual table to Audit table.

Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
  ,insertdate datetime
  ,insertedby varchar(100)
) ; 


Notice that I have kept all the same column in customer_Audit what we have in customer table. Also I have added two more column insertdate and insertedby.

Now let's create After Insert Trigger. From here you can get the idea how your After Insert Trigger will look like.

Create AFTER INSERT Trigger in MySQL:



DELIMITER //

CREATE TRIGGER customer_after_insert
AFTER insert
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);

   -- Get user name who is performing insert
   SELECT USER() INTO var_User;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,insertdate,insertedby)
    
   VALUES
   ( new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is inserting the record. We used SYSDATE() function to record the datetime for insert  All the other columns are populated from the values which are inserted into customer table.

Insert some rows in customer table and then query "customer_audit" table, you will see the same records with insert date and inserted by.

How to create after delete trigger in MySQL - MySQL Developer Tutorial

How to create after delete trigger in MySQL

Often we want to know who has deleted the records from table. In MySQL we can use After Delete Trigger to capture deleted row and insert into some audit table.  Many of the companies have the requirement to keep tract of deleted records and After Delete Trigger in MySQL works great.

Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
  ,deletedate datetime
  ,deletedby varchar(100)
) ; 


Notice that I have kept all the same column in customer_Audit what we have in customer table. Also I have added two more column deletedate and deletedby.

Now let's create After Delete Trigger. From here you can get the idea how your After Delete Trigger will look like.

Create After Delete Trigger in MySQL:



DELIMITER //

CREATE TRIGGER customer_after_delete
AFTER DELETE
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);

   -- Get user name who is performing delete
   SELECT USER() INTO var_User;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,deletedate,deletedby)
    
   VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is deleting the record. We used SYSDATE() function to record the datetime for delete. All the other columns are populated from old values which are deleted from customer table.

Delete some records from customer table.

Delete from customer where idcustomer in (1,2);


Once you delete the records and run below query for customer_audit. you will see below records.
MySQL After Delete Trigger  | MariaDB After Delete Trigger example