SQL Server High Availability on Azure Tutorial

How to Configure SQL Server Availability Group in MS Azure

How to Configure SQL Server Availability Group in MS Azure

How to Configure SQL Server Availability Group in MS Azure explains all below steps

  1. How to enable AlwaysOn Availability in SQL Server?
  2. How to prepare database to be added in Availability group?
  3. How to change SQL Server Service accounts ?
  4. Resolved: Can't create Availability Group
  5. Resolved: Account doesn't have permission to access end points
  6. Resolved: Joining takes long time and then fails
How to configure SQL Server Availability Group in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to create SQL Server High Availability Listener in MS Azure explains 

  1. How to create SQL Server High Availability Listener in MS Azure?
  2. Error: Cannot connect to High Availability Listener
  3. Resolution: Why can't I connect to HA listener name from Secondary Replica Azure?
  4. How to configure Static IP of HA Listener in Azure?

How to create SQL Server High Availability Listener in Microsoft Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure - SQL Server High Availability on Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure explains all below steps
  1. How to configure Cluster Quorum Cloud witness in MS Azure?
  2. How to access Storage Account in MS Azure?
  3. How to access Access key in MS Azure? 
  4. Resolved: Access key can't be valided
  5. Resolved: An error occurred while validating access key to Azure
  6. Resolved: An error occurred while validating access to Azure from cluster node

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure - SQL Server HA on Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure explains all below steps.

  1. How to create load balancer in Azure Portal?
  2. How to Configure Internal Load Balancer in Azure? 
  3. How to Configure FrontEndIP of Load balancer in Azure?
  4. How to configure backend pool in Load balancer in Azure?
  5. How to configure Health Probes in Azure?
  6. How to configure Load Balancer for Availability Group Listener in Azure?
  7. How to Configure AG listener port in Load Balancer in Azure? 
  8. How to Configure Cluster to use Load balancer in Azure?

Script Used in this video: 
$AG = Get-ClusterResource | Where-Object { $_.resourcetype -eq "SQL Server Availability Group" }
$Listener = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "Network Name" }
$IP = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "IP Address" }

Get-ClusterResource $IP.Name | Set-ClusterParameter -Multiple @{"ProbePort"="80";"OverrideAddressMatch"=1;}
Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "PublishPTRRecords" -Value 1
Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "HostRecordTTL" -Value 60


How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to Create SQL Server Availability Group Cluster in MS Azure explain all below steps

  1. How to add Failover clustering feature of windows in Azure?
  2. How to Prepare nodes to be added in cluster in Azure?
  3. How to run custom cluster configuration tests in Azure?
  4. How to Change IP address of Cluster in DNS in Azure?
  5. How to resolve IP conflict of Cluster in Azure? 
  6. How to start cluster services on Nodes in Azure?
  7. How to start cluster forcefully in Azure? 
  8. How to change Cluster IP in Cluster Manager in Azure?
How to create SQL Server Availability Group Cluster in MS Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure explains all below steps.

1. How to create SQL Server Virtual Machine in Azure?
2. How to create new Availability Group for SQL Server Virtual Machines in Azure?
3. How to setup Static IP of SQL Server Virtual Machine in Azure?
4. How to Setup Custom DNS of a virtual Machine in Azure?
5. How to add Azure Virtual Machine to a domain?
6. How to setup memory of SQL Server in Azure?

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create Virtual Machine for Domain Controller in Azure

How to create Virtual Machine for Domain Controller in Azure

How to create Virtual Machine for Domain Controller in Azure video explains all below topics
  1. How to create virtual machine in MS Azure? 
  2. How to create Windows Virtual Machine in Azure?
  3. How to create resource group in Azure?
  4. How to create Virtual Network in Azure?
  5. How to create Storage Account in Azure?
  6. How to create new disk in Azure?
  7. How to create Availability Set in Azure?
  8. How to open specific open ports in Azure Virtual Machine?
How to create Virtual Machine for Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure explains below topics
  1. How to configure DNS of Azure Virtual machine?
  2. Resolved error: Domain Controller can't be contacted in Azure
  3. Adding virtual Machine to existing Domain Controller to act as Domain as well 
  4. How to setup replication between two domain Controllers in Azure?
  5. How to create user in Active Directory in Azure?

How to add second Domain Controller to an existing Domain Controller in Azure

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

How to create your own Domain Controller in MS Azure video explains all below steps in detail

  1. How to connect to virtual machine in MS Azure?
  2. How to configure Virtual Machine's static IP in MS Azure?
  3. How to Enable/Disable Ports and protocol in MS Azure?
  4. How to configure your Own DNS and Active Directory in Azure?

How to create your own Domain Controller in MS Azure

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial


Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial explains all below steps for SQL Server on Azure starting point

  1. Setting up SQL Server High Availability in MS Azure Portal
  2. How to access MS Azure Portal?
  3. How to setup free Account of MS Azure?
  4. Introduction to MS Azure Portal components

What is MS Azure Portal and How to Setup step by step

How to manually failover SQL Server Availability Group in Linux CentOS 7 - SQL Server Linux Tutorial

How to manually failover SQL Server Availability Group in Linux CentOS 7

How to manually failover SQL Server Availability Group in Linux CentOS 7 explains all the steps you need to perform to failover Availability Group in SQL Server when installed on Linux. You will learn below items in this video 1-- Check Constraints a) Location Constraint b) Ordering Constraint 2 -- Failover Availability Group to Specific Node 3 -- Remove Location Constraint to remove preferred node settings. Scripts used in this video: # Check current owner node of SQL Sever AG pcs status # Check Constraints pcs constraint list --full # Move SQL Server Availability Group resources from One node to another pcs resource move TBSLinuxRG-master TBSLinuxNode2 --master # Remove location constraint for successful Failover pcs constraint remove cli-prefer-TBSLinuxRG-master


How to manually failover SQL Server Availability Group in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7- SQL Server on Linux Tutorial

How to configure SQL Server High Availability Groups in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7 explains all below steps to configuration SQL Server High Availability installed on Linux. Installing SQL Server High Availability Package Installing and Enabling SQL Server Agent if its not installed and enabled already Enable SQL server High Availability on each Node Creating AG Group EndPoint and Certificates Copy Certificates of each node into all other Nodes Change ownership and group association to mysql(User) Restore each Certificate with authenticated user ( create user if you don't have already one) Grant AG Group using SSMS Create SQL Server Login and Permission for Pacemaker Create Availability Group resource in pacemaker Create IP for Listener in PackeMaker Create Listener using same IP Test Failover Script used in this video: # Install SQL Server High Availability Package sudo yum install mssql-server-ha # Enable AlwaysOn Avaiability Groups and resetart SQL Server on both nodes sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server # Open SSMS and create Certificate for each node # Node Name : TBSLinuxNode1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode1_Cert WITH SUBJECT = 'TBSLinuxNode1 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode1_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode1_Cert, ROLE = ALL); GO #Now samething on Node2 (TBSLinuxNode2) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode2_Cert WITH SUBJECT = 'TBSLinuxNode2 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode2_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode2_Cert, ROLE = ALL); GO # Copy Certificate of one node to other using SCP # on Node1 scp -r root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer # On Node 2 scp -r root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer # Change Ownership of certificate to mssql on each node(In my case I have only two nodes) sudo chown mssql:mssql TBSLinuxNode2_Cert.cer sudo chown mssql:mssql TBSLinuxNode1_Cert.cer # Create instance Level SQL User (TBSAGUser in my case on each node) using SSMS Open SSMS and create User # Restore certificate of Other Nodes into the present node using SSMS below: Login to TBSLinuxNode1 CREATE CERTIFICATE TBSLinuxNode2_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode1 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Let's do the same thing by connecting to TBSLinuxNode2 and restore TBSLinuxNode1.cert CREATE CERTIFICATE TBSLinuxNode1_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode2 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Create Availability Group using SSMS with Cluster type External # Create a new login or use the same login to give Pacemaker permission and provide view server permission, I will give #sysadmin to this user just for this demo # On all Nodes Edit vi /var/opt/mssql/secrets/passwd using emacs and update with user and password that you created for Pacemaker and save it TBSAGUser Pass@123 # Hold down the CTRL key and then press X, then C, to exit and save the file # setup right permission sudo chmod 400 /var/opt/mssql/secrets/passwd # Create the AG resource in the Pacemaker cluster sudo pcs resource create TBSLinuxRG ocf:mssql:ag ag_name=TBSLinuxAG meta failure-timeout=30s --master meta notify=true # Create IP resource for Listener sudo pcs resource create LinuxSQLProdList ocf:heartbeat:IPaddr2 ip=192.168.1.104 cidr_netmask=24 # Create an ordering constraint to ensure that the AG resource is up and running before the IP address. While the colocation #constraint implies an ordering constraint, this enforces it sudo pcs constraint order promote TBSLinuxRG-master then start LinuxSQLProdList




How to configure SQL Server High Availability Group in Linux CentOS 7

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to Install cluster pacemaker on linux CentOS 7 for SQL Server High Availability video explains below Validating Nodes IP address Disable firewall and SELINUX (for Installation Only) -- Not Recommended for Prod Installing pacemaker Configuring Cluster User, i.e. hacluster Starting and Enabling Pacemaker services Adding members node to cluster Creating and starting cluster Confuring cluster resources a) Virtual IP of the cluster b) Apache Services -httpd-- Just to test the failover cluster Create Constraint of cluster resources Test Fail-over Script used in this video: #How to create Pacemaker Cluster on Linux CentoS 7 # IP Configuration verification of all possible nodes # installing httpd to verify in the end of cluster on both nodes yum install httpd -y # Disable SELINUX and Firewall vi /etc/sysconfig/selinux Change SELINUX=enforcing to Permissive and save the file on both nodes and setenforce 0 systemctl stop firewalld systemctl disable firewalld # Install Pacemaker software on both nodes yum install pacemaker pcs fence-agents-all -y # Once installed, you will see user created named hacluster - we need reset the password for this user on both nodes tail /etc/passwd --- To see if user is created passwd hacluster # Start and Enable PaceMaker Services on both nodes systemctl start pcsd systemctl enable pcsd # Add membership of the cluster using hacluster username pcs cluster auth TBSLinuxNode1 TBSLinuxNode2 -u hacluster # Create Cluster named "LinuxSQLCluster" in my case pcs cluster setup --name LinuxSQLCluster TBSLinuxNode1 TBSLinuxNode2 # View Configuration of the Cluster on both nodes Cat /etc/corosync/corosync.conf #Start the cluster pcs cluster start --all #Check the status of the cluster and enable the cluster pcs status pcs cluster enable -all # Configure the resources of the cluster a) Virtual IP of the cluster pcs resource create VirtIP IPAddr ip=192.168.1.102 cidr_netmask=24 op monitor interval=60 b) apache services (To check the failover, not necessary for SQL Server) pcs resource create Httpd apache Configuration="/etc/httpd/conf/httpd.conf" op monitor interval=60s -- # Check the status again to see if resources are configured right and you will notice both resources are created and in stopped state # Let's create Constraint and Policies on the cluster pcs constraint colocation add Httpd with VirtIP INFINITY pcs property set stonith-enabled=false pcs property set no-quorum-policy=ignore pcs property set default-resource-stickiness="INFINITY" # Check the status again to see if resources are online pcs status # Check virtual IP on active node ip a # Now let's create an HTML page to see where which node our resources are running vi /var/www/html/index.html #Type anything for example Hey I am TBSClusterNode1 (if creating on TBSLinuxNode1 in my case) etc. and save it on each node # Now let's view the page using IE or any browser # Let's failover the cluster and recheck the html page file we just created pcs cluster stop TBSLinuxNode1 (If it is running on node1, if node2, stop cluster services on that node)

How to Install cluster pacemaker on Linux CentOS 7 for SQL Server High Availability



How to Configure SQL Server Windows Authentication in Linux CentOS 7 - SQL Server on Linux Tutorial

How to Configure SQL Server Windows Authentication in Linux CentOS 7


How to Configure SQL Server Windows Authentication in Linux CentOS 7 video explains all below steps
Create Active Directory Service Account for SQL Server Setup SPN for SQL Server AG Service Account Validating Key version Number (kvno) Create MSSQLScv Keytab Setting Proper Permission of Keytab Configure SQL Server Instance to use Keytab file for kerberos Authentication Restarting SQL server and Testing Using SSMS Script Used in this Demo:


#Create SQL Server Service Account in Your Active Directory Domain UserName= mssql PasswordExpire= never # Setting ServicePrincipalName (SPN) for SQL Server Service account on Your Domain Conroller setspn -A MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 mssql # Checking KVNO (Key Version Number) number for AD Account-usually its 2 kinit mssql@TECHBROTHERS.LOCAL kvno MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 # Creating SQL Keytab File (AD Account with password must set this up) using ktutil sudo ktutil ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e aes256-cts-hmac-sha1-96 ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e rc4-hmac ktutil: wkt /var/opt/mssql/secrets/mssql.keytab quit # Setting up Proper permission (ownership) sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab # Configuring SQL Server to Point to KeyTab File sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab # Restarting SQL Server Services sudo systemctl restart mssql-server # Connecting to SSMS and Testing Windows Authentication


How to Configure SQL Server Windows Authentication in Linux CentOS 7

How to Install SQL Server 2017 on CentOS 7 Step by Step

How to Install SQL Server 2017 on CentOS7 Step by Step

In this video we are going to learn how to install SQL Server on Linux Step by Step. How to Configure SQL Server Repository Download SQL Server Red Hat Repository configuration File Install SQL Server using yum Command Configure SQL Server and setup SA Password Restart SQL Server services Open Firewall port to allow remote connection to SQL Server Enable and start SQL Server Agent Services Download and Install SQL server Tools to connect to SQL Server Connecting to SQL Server using SSMS 2017 with SQL Server Authentication Scripts used in this video: # Let's check if mssql-server repository already exists? sudo ls /etc/yum.repos.d | grep mssql-server.repo # If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install sudo cat/etc/yum.repos.d/mssql-server.repo sudo rm -rf /etc/yum.repos.d/mssql-server.repo # Configure and Download SQL Server Repository sudo curl -o /etc/yum.repos.d/mssql-server.repo
https://packages.microsoft.com/config... # Install SQL Server using below commands sudo yum install -y mssql-server # Configure SQL Server and Setup SA password sudo /opt/mssql/bin/mssql-conf setup # Restart SQL Server Services systemctl restart mssql-server # Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config... sudo yum install -y mssql-tools unixODBC-devel #Set Environment Tools to your Path Environment # Connect to SQL Server using sqlcmd tool sqlcmd -S localhost -U SA -P Pass@123 # Connect to SQL Server Remotely using SQL Server Authentication SSMS - Error and Resolution # Enable Firewall port 1433 sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload # Enable and start SQL Server Agent services sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

How to Install SQL Server 2017 on CentOS 7



SQL Server on Linux Tutorial

How to Join CentOS 7 to an Existing Windows Domain

How to Join CentOS 7 to an Existing Windows Domain


In this video, you will learn How to Join CentOS 7 to an Existing Windows Domain. This video is part of effort in which we finally will install SQL Server on Linux Box. In our last video, you learn how to install Linux CentOS from starch. Script used in this video:



# Installing realmd package






# yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python

Edit Host File to add DNS IP address and Server Information

# vi /etc/hosts


View File /etc/resolv.conf It should resolve Domain name and IP address Join with Windows Domain

#realm join --user=clusteradmin tbsdc.Techbrothers.local


Verify domain Join

# realm list



# id clusteradmin@Techbrothers.local


Turning off Fully Qualified Name requirement of AD user

# vi /etc/sssd/sssd.conf


systemctl restart sssd systemctl daemon-reload
Adding user to sudo or admin - Where "wheel" is group and cluster admin is active directory user

# sudo usermod -a -G wheel clusteradmin



How to Join Linux CentOS 7 to Windows Active Directory

How to Install Linux CentOS 7 for SQL Server Installation

How to Install Linux CentOS 7 for SQL Server Installation


In this video you are going to learn how to install Linux Operation System for SQL Server Installation. below are the items you are going to learn in this step by step video for CentOS installation.
  • Download CentOS 7 ISO (Minimul ISO)
  • Create Bootable Flash Drive to boot Physical Machine
  • Boot Server from BIOS with Bootable Flash Drive
  • Setting up Language in CentOS
  • Setting up Installation Destination Media
  • Network Configuration
  • User Configuration
  • Test Installation
Download CentoS 7.6 Link:
https://www.centos.org/download/





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