Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

Important SQL string functions

Suppose you have database name student and there you have a table named student_table_1. In this table you have columns like Id, Name, Class, Section, Father_name, Mother_name.

1. UPPER()/UCASE() function

Convert string into upper case. This function will not change the existing column but will create a new column which string character will be in upper case.

Syntax:
SELECT UPPER(col_Name) AS new_col_name

Example:
SELECT UPPER(Name) AS NAME_2

Here Name is that column which names I wanted to convert into upper case. Because UPPER function creates a new column after converting the words into upper case so NAME_2 is the new column.

2. LOWER()/LCASE function

Convert string into lower case. This function will not change the existing column but will create a new column which string character will be in lower case.

Syntax:
SELECT LOWER(col_Name) AS new_col_name

Example:
SELECT LOWER(Name) AS NAME_2

Here Name is that column which names I wanted to convert into lower case. Because LOWER function creates a new column after converting the words into lower case so NAME_2 is the new column.

3. CHAR_LENGTH() function

This function will show the length of the characters present in a column cells. This function will create a new the column where you will see the characters length.

Syntax:
SELECT CHAR_LENGTH(col_Name) AS new_col_name

Example:
SELECT CHAR_LENGTH(Name) AS char_length

Here Name is that column which character length I wanted to get. Because CHAR_LENGTH function creates a new column after converting calculating the character length, so char_length is the new column.

4. CONCAT() function

To concatenate multiple columns this function is used.

Syntax:
SELECT CONCAT(col_name,col_name...) AS new_col_name

Example:
SELECT CONCAT(Name,Class) AS concatenate_col

To get space between words:
SELECT CONCAT(Name," ",Class) AS NAME_2

You can use any separator like -, /, _, etc.

Syntax:
SELECT CONCAT(col_name," - ",col_name...) AS new_col_name

Example:
SELECT CONCAT(Name," - ",Class," / ",Id) AS concatenate_col

Here Name, Class and Id are are those columns which I wanted to concatenate. Because CONCAT function creates a new column after doing concatenate, so concatenate_col is the new column name.

5. LTRIM() function

This function will remove the extra spaces present in the left side of string or numeric value.

Syntax:
SELECT LTRIM(col_name) AS new_col_name

Example:
SELECT LTRIM(Name) AS NAME_2

LTRIM function creates a new column after trimming, so NAME_2 is the new column name.

6. RTRIM() function

This function will remove the extra spaces present on the right side of the string or numeric value.

Syntax:
SELECT RTRIM(col_name) AS new_col_name

Example:
SELECT RTRIM(Name) AS NAME_2

RTRIM function creates a new column after trimming, so NAME_2 is the new column name.

7. TRIM() function

This function will remove the extra spaces present on both sides of the string or numeric value.

Syntax:
SELECT TRIM(col_name) AS new_col_name

Example:
SELECT TRIM(Name) AS NAME_2

TRIM function creates a new column after trimming, so NAME_2 is the new column name.

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us