String Functions in Sql
1)ASCII Function:
Returns the ASCII value of the first character from Employees table.
Syntax : Select ASCII(Column Name) from table;
Example :-Getting ASCII values of Column LAST_NAME from Employees table
Syntax: select ASCII(LAST_NAME)from Employees;
Output is shown below
2)CHAR Function:
Returns the character based on the number code.
Syntax : Select CHAR(Number Code);
Example : Getting character for the number code 65.
Syntax : select CHAR(65);
Output is shown below
3)CharIndex Function:
Search for the string in the given string an returns the forst position.
Syntax : Select Charindex(Substring,Main String);
Example : Getting the first position of G from the string Hire Force Global
Syntax : select CHARINDEX(‘G’,’Hire Force Global’);
Output is shown below
4)String Concat Function:
Add two strings together
Syntax : Select Concat(First String,Second String);
Example : Concatenating First Name and Last Name from Employees table.
Syntax: select concat(FIRST_NAME,LAST_NAME) from Employees;
Output is shown below
5)Concat_WS() Function:
Add strings together. Use a separator to concatenate the string values.
Syntax : Select Concat_WS(Seperator,String1,String2,….);
Example : Concatinating string Hire Force Global with * separator.
Syntax : select CONCAT_WS(‘*’,’Hire’,’Force’,’Global’);
Output shown below
6)Concat with + Function:
Add 2 strings together
Syntax : select (String1 +String2);
Example : Concat the strings Hire Force Global together
Syntax : select (‘Hire’ + ‘Force’ + ‘Global’);
Output shown below
7)Data length Function:
Returns data length of an expression
Syntax : select datalength(string);
Example : Getting data length of the string Hire Force Global
Syntax : select DATALENGTH(‘Hire Force Global’);
Output shown below
8)Upper() Function:
Converts the complete string into Upper case(Capital letters)
Syntax : Select Upper(‘String’) from Table name;
Example : Converting First Name in Employees table to upper case
Syntax : select Upper(First_Name) from Employees;
Output is shown below
9)Lower() Function:
Converts the complete string into lower case(small letters)
Syntax : Select Lower(‘String’) from table name;
Example : Converting last Name in Employees table to lower case
Syntax : select Lower(Last_Name) from Employees;
Output is shown below
10)LTrim Function:
Remove leading space from a trim
Syntax : Select LTrim(String) from table Name
Example : Remove leading space from string Hire Force Global
Syntax: select ltrim(‘ Hire Force Global’)from dual;
Output shown below
11)RTrim Function:
Remove trailing spaces from a string
Syntax : Select RTrim(String) from table Name
Example : Remove trailing space from string Hire Force Global
Syntax: select Rtrim(‘Hire Force Global ‘)from dual;
Output shown below
12)Replace Function:
Replaces a string with another string
Syntax : Replace(String,Old String,New String);
Example: Replace G with T in Hire Force Global.
Syntax : select replace(‘Hire Force Global’,’G’,’T’) from dual;
Output shown below
13)Replicate Function:
Replicate function replicates a string a specified number of times.
Syntax : select Replicate(String,Number);
Example : Replicating string Hire Force Global 5 times
Syntax : select REPLICATE(‘Hire Force Global’,5);
Output shown below
14)String Reverse Function:
It reverses a string.
Syntax : select reverse(String);
Example : Reversing the string “Hire Force Global”
Syntax : select Reverse(‘Hire Force Global’);
Output shown below
15)Substring Function:
The Substring function extracts some character from a string
Syntax : select substring(String,start,length);
Example : Extracting a substring from “Hire Force Global” starting from 3 upto 4 characters
Syntax : select SUBSTRING(‘Hire Force Global’,3,4);
Output shown below
