My Blog

SQL String Functions

—

by

Awais Khan
in

In this article, you will learn about the various string functions of Structured Query Language in detail with examples.

What are String Functions in SQL?

SQL String functions are the predefined functions that allow the database users for string manipulation. These functions only accept, process, and give results of the string data type.

Following are the most important string functions in Structured Query Language:

  1. ASCII()
  2. CHAR_LENGTH()
  3. CHARACTER_LENGTH()
  4. CONCAT()
  5. CONCAT_WS()
  6. FIND_IN_SET()
  7. FORMAT()
  8. INSERT()
  9. INSTR()
  10. LCASE()
  11. LEFT()
  12. LOCATE()
  13. LOWER()
  14. LPAD()
  15. LTRIM()
  16. MID()
  17. POSITION()
  18. REPEAT()
  19. REPLACE()
  20. REVERSE()
  21. RIGHT()
  22. RPAD()
  23. RTRIM()
  24. SPACE()
  25. STRCMP()
  26. SUBSTR()
  27. SUBSTRING()
  28. SUBSTRING_INDEX()
  29. UCASE()
  30. UPPER()

Let’s discuss each string function in brief with the SQL table.

Now, we create a new table in SQL, which helps to understand each string function. The syntax for creating a new table in the SQL database is as follows:

   CREATE TABLE table_name  

(  

1st_Column Data Type (character_size of 1st Column),    

2nd_Column Data Type (character_size of the 2nd column ),    

3rd_Column Data Type (character_size of the 3rd column),    

...   

       Nth_Column Data Type (character_size of the Nth column)  

);   

      The following CREATE statement creates the Faculty_Info table:

      CREATE TABLE Faculty_Info  

(  

Faculty_ID INT NOT NULL PRIMARY KEY,    

Faculty_First_Name VARCHAR (100),    

Faculty_Last_Name VARCHAR (100),    

Faculty_Dept_Id INT NOT NULL,  

Faculty_AddressVarchar(120),  

Faculty_City Varchar (80),  

Faculty_Salary INT   

);  

        The following INSERT queries insert the records of college Faculties in the Faculty_Info table:

        INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000); 

          The following SELECT statement displays the inserted records of the above Faculty_Info table:

          SELECT * FROM Faculty_Info;   
          Faculty_IdFaculty_First_NameFaculty_Last_NameFaculty_Dept_IdFaculty_AddressFaculty_CityFaculty_Salary
          1001ArushSharma4001Aman ViharDelhi20000
          1002BulbulRoy4002Nirman ViharDelhi38000
          1004SaurabhRoy4001Sector 128Mumbai45000
          1005ShivaniSinghania4001Vivek ViharKolkata42000
          1006AvinashSharma4002Sarvodya CalonyDelhi28000
          1007ShyamBesas4003Krishna NagarLucknow35000

          ASCII String Function

          This function in SQL returns the ASCII value of the character in the output. It gives the ASCII value of the left-most character of the string.

          Syntax of ASCII String Function:

          Syntax1: This syntax uses ASCII with the table column:

          SELECT ASCII(Column_Name) as ASCII_Name FROM Table_Name;  

          Syntax2: This syntax uses ASCII with the string:

          SELECT ASCII(String);  

          Syntax3: This syntax uses ASCII with the character:

          SELECT ASCII(Character);  

          Example of ASCII String function:

          The following SELECT query uses ASCII code with the Faculty_City column of the above Faculty_Info table.

          SELECT Faculty_City, ASCII(Faculty_City) AS ASCII_code_of_column FROM Faculty_Info;  

          This query shows the ASCII code of the first character of all cities of the Faculty_City column.

          Faculty_CityASCII_Code_of_column
          Delhi68
          Delhi68
          Mumbai77
          Kolkata75
          Delhi68
          Lucknow76

          CHAR_LENGTH String Function

          This string function returns the length of the specified word. It shows the number of characters from the word.

          Syntax of CHAR_LENGTH String Function:

          Syntax1: This syntax uses CHAR_LENGTH() with the table column:

          SELECT CHAR_LENGTH(Column_Name) as Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses CHAR_LENGTH() with the word:

          SELECT CHAR_LENGTH(word);  

          Examples of CHAR_LENGTH String function:

          Example 1: This example shows the number of characters of the JavaTpoint word:

          SELECT CHAR_LENGTH('JavaTpoint');  

          Output:

          10

          Example 2: This example uses CHAR_LENGTH() with the Faculty_Last_Name column of the above Faculty_Info table.

          SELECT Faculty_Last_Name, CHAR_LENGTH(Faculty_Last_Name) AS Length_of_Last_Namecolumn FROM Faculty_Info;  

          This query shows the total number of characters of the last name of each faculty.

          Output:

          Faculty_Last_NameLength_of_Last_Namecolumn
          Sharma6
          Roy3
          Roy3
          Singhania9
          Sharma6
          Besas5

          CHARACTER_LENGTH String Function

          This string function returns the length of the given string. It shows the number of all characters and spaces from the sentence.

          Syntax of CHARACTER_LENGTH String Function:

          Syntax1: This syntax uses CHARACTER_LENGTH() with the table column:

          SELECT CHARACTER_LENGTH(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses CHARACTER_LENGTH() with the string:

          SELECT CHARACTER_LENGTH(String);  

          Examples of CHARACTER_LENGTH String function:

          Example 1: The following SELECT query shows the total number of characters and spaces of the specified string:

          SELECT CHARACTER_LENGTH('JavaTpoint is a good company');  

          Output:

          28

          Example 2: The following SELECT query uses CHARACTER_LENGTH() with the Faculty_Addresss column of the above Faculty_Info table.

          SELECT Faculty_Address, CHARACTER_LENGTH(Faculty_Address) AS Length_of_Address_column FROM Faculty_Info;  

          This SQL statement shows the total number of characters and spaces of the address of each faculty.

          Output:

          Faculty_AddressLength_of_Address_column
          Aman Vihar10
          Nirman Vihar12
          Sector 12810
          Vivek Vihar11
          Sarvodya Calony15
          Krishna Nagar13

          CONCAT String Function

          This string function concatenates two strings or words and forms a new string in the result.

          Syntax of CONCAT String Function:

          Syntax1: This syntax uses CONCAT() with table columns:

          SELECT CONCAT(Column_Name1, Column_Name2, ..... column_NameN) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses CONCAT() with multiple strings:

          SELECT CONCAT(String_1, String_2, String_3, ...., String_N);  

          Examples of CONCAT string function:

          Example 1: The following SELECT query appends the multiple strings into a single string:

          SELECT CONCAT('JavaTpoint', ' is', ' a', ' good', ' company.');  

          Output:

          JavaTpoint is a good company

          Example 2: The following SELECT query uses CONCAT() with the Faculty_First_Name and Faculty_Last_Name columns of above Faculty_Info table:

          SELECT Faculty_First_Name, Faculty_Last_Name CONCAT(Faculty_First_Name, Faculty_Last_Name) AS Append_First_LastName FROM Faculty_Info;  

          This SQL statement merges the first name and last name of each faculty as shown in the below table:

          Output:

          Faculty_First_NameFaculty_Last_NameAppend_First_LastName
          ArushSharmaArush Sharma
          BulbulRoyBulbul Roy
          SaurabhRoySaurabh Roy
          ShivaniSinghaniaShivani Singhania
          AvinashSharmaAvinash Sharma
          ShyamBesasShyam Besas

          CONCAT_WS String Function

          This string function concatenates multiple strings or words with the help of concatenating symbol. This function uses another parameter that denotes the concatenate symbol.

          Syntax of CONCAT_WS String Function:

          Syntax1: This syntax uses CONCAT_WS() with table columns:

          SELECT CONCAT_WS( Concatenate_symbol, Column_Name1, Column_Name2, ..... column_NameN) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses CONCAT_WS() with multiple strings:

          SELECT CONCAT_WS(Concatenate_symbol, String_1, String_2, String_3, ...., String_N);  

          Examples of CONCAT_WS String function:

          Example 1: The following SELECT query appends the multiple strings using the plus (+) symbol:

          SELECT CONCAT_WS('+', 'JavaTpoint', ' is', ' a', ' good', ' company');  

          Output:

          JavaTpoint+is+a+good+company

          Example 2: The following SELECT query uses CONCAT_WS() with the Faculty_First_Name and Faculty_Last_Name columns of the above Faculty_Info table:

          SELECT Faculty_First_Name, Faculty_Last_Name CONCAT_WS('.', Faculty_First_Name, Faculty_Last_Name) AS Append_First_LastName FROM Faculty_Info;  

          This SQL statement merges the first name and last name of each faculty by the dot symbol.

          Output:

          Faculty_First_NameFaculty_Last_NameAppend_First_LastName
          ArushSharmaArush.Sharma
          BulbulRoyBulbul.Roy
          SaurabhRoySaurabh.Roy
          ShivaniSinghaniaShivani.Singhania
          AvinashSharmaAvinash.Sharma
          ShyamBesasShyam.Besas

          FIND_IN_SET String Function

          This string function allows you to find the position of the searched_string in the set of strings.

          Syntax of FIND_IN_SET String Function:

          SELECT FIND_IN_SET(Concatenate_symbol, String_1, String_2, String_3, ...., String_N);  

          Examples of FIND_IN_SET String function:

          Example 1: The following SELECT query searches ‘a’ character from the given set of characters:

          SELECT FIND_IN_SET('a', 'JavaTpoint, is, a, good, company');  

          Output:

          3

          Example2: The following SELECT query searches ‘Delhi’ string from the given set of strings:

          SELECT FIND_IN_SET('Delhi', 'Mumbai, Goa, Banglore, Delhi, Kolkata, Chennai');  

          Output:

          4

          FORMAT String Function

          This String function allows you to display the given string in the specified format.

          Syntax of FORMAT String Function:

          Syntax1: This syntax uses FORMAT() with table column:

          SELECT FORMAT(Column_Name1, Format_String) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses FORMAT() with the string:

          SELECT FORMAT(String_1, Format_String);  

          Examples of FORMAT String function:

          Example 1: The following SELECT query displays the number in the percentage format:

          SELECT FORMAT('0.958', 'Percent');  

          Output:

          95.80%

          Example 2: The following SELECT query uses FORMAT() with the Faculty_Salary column of the above Faculty_Info table:

          SELECT Faculty_Salary, FORMAT(Faculty_Salary, 'C') AS Currency_Salary FROM Faculty_Info;  

          This SQL statement displays the salary of each faculty in the currency format.

          Output:

          Faculty_SalaryCurrency_Salary
          20000$20000.00
          38000$38000.00
          45000$45000.00
          42000$42000.00
          28000$28000.00
          35000$35000.00

          INSERT String Function

          This string function allows the database users to insert the sub-string in the original string at the given index position.

          Syntax of INSERT String Function:

          Syntax1: This syntax uses INSERT() with the column of the SQL:

          SELECT INSERT(Column_Name, Position, Number, String) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses INSERT() with the string:

          SELECT INSERT(String_1, Position, Number, String_2);  

          Examples of INSERT String function:

          Example 1: The following SELECT query inserts the ‘Tpoint’ string at the fifth position in the ‘JavaExcel’ string:

          SELECT INSERT('JavaExcel', 5, 6, 'Tpoint');  

          Output:

          JavaTpointExcel

          Example 2: The following SELECT query uses INSERT() with the Faculty_City column of the above Faculty_Info table:

          SELECT Faculty_City, INSERT(Faculty_City, 3, 4, 'Agra') AS Insert_Agra FROM Faculty_Info;  

          This SQL statement inserts the Agra string at the third position in the city of each faculty.

          Output:

          Faculty_CityInsert_Agra
          DelhiDeAgralhi
          DelhiDeAgralhi
          MumbaiMuAgrambai
          KolkataKoAgralkata
          DelhiDeAgralhi
          LucknowLuAgracknow

          INSTR String Function

          This string function returns the index value of the first occurrence of the given character in the string.

          Syntax of INSTR String Function:

          Syntax1: This syntax uses INSTR() with the column of the SQL:

          SELECT INSTR(Column_Name, character) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses INSTR() with the string:

          SELECT INSTR(String, character);  

          Examples of INSTR String function:

          Example 1: The following SELECT query shows the index value of the ‘T’ character in the JavaTpoint string

          SELECT INSTR('JavaTpoint', 'T');  

          Output:

          5

          Example 2: The following SELECT query uses INSTR() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, INSTR(Faculty_Address, 'a') AS INSTR_Address FROM Faculty_Info;  

          This SQL statement converts the cities of all faculties into lower case letters.

          Output:

          Faculty_AddressLCASE_Address
          Aman Vihar3
          Nirman Vihar5
          Sactor 1282
          Vivek Vihar10
          Sarvodya Calony2
          Krishna Nagar7

          LCASE String Function

          This string function allows users to convert the specified string into lower case letters.

          Syntax of LCASE String Function:

          Syntax1: This syntax uses LCASE() with the column of the SQL table:

          SELECT LCASE(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LCASE() with the string:

          SELECT LCASE(String);  

          Examples of LCASE String function:

          Example 1: The following SELECT query converts the upper case letters of the given string into the lower case letters.

          SELECT LCASE( 'The CAPITAL of INDIA is NEW DELHI');  

          Output:

          the capital of india is new delhi

          Example 2: The following SELECT query uses LCASE() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, LCASE(Faculty_Address) AS LCASE_Address FROM Faculty_Info;  

          This SQL statement converts the cities of all faculties into lower case letters.

          Output:

          Faculty_AddressLCASE_Address
          Aman Viharaman vihar
          Nirman Viharnirman vihar
          Sector 128sector 128
          Vivek Viharvivek vihar
          Sarvodya Calonysarvodya colony
          Krishna Nagarkrishna nagar

          LEFT String Function

          This string function shows the leftmost characters from the given string. It reads the characters to the given index position.

          Syntax of LEFT String Function:

          Syntax1: This syntax uses LEFT() with the column of the SQL table:

          SELECT LEFT(Column_Name, Index_position) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LEFT() with the string:

          SELECT LEFT(String, Index_position);  

          Examples of LEFT String function:

          Example 1: The following SELECT query shows the 11 leftmost characters from the given string:

          SELECT LEFT( 'The CAPITAL of INDIA is NEW DELHI', 11);  

          Output:

          The CAPITAL

          Example 2: The following SELECT query uses LEFT() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, LEFT(Faculty_Address, 6) AS LEFT_Address FROM Faculty_Info;  

          This SQL statement shows the 6 leftmost characters from the address of all faculties

          Output:

          Faculty_AddressLEFT_Address
          Aman ViharAman V
          Nirman ViharNirman
          Sector 128Sector
          Vivek ViharVivek
          Sarvodya CalonySarvod
          Krishna NagarKrishn

          LOCATE String Function

          This string function shows the index value of the first occurrence of the word in the given string.

          Syntax of LOCATE String Function:

          Syntax1: This syntax uses LOCATE() with the column of the SQL table:

          SELECT LOCATE( Search_string, Column_Name, Search_position) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LOCATE() with the string:

          SELECT LOCATE(Search_string, String Search_position);  

          Examples of LOCATE String function:

          Example 1: The following SELECT query shows the index value of the INDIA word in the given sentence:

          SELECT LOCATE('INDIA','The CAPITAL of INDIA is NEW DELHI ', 1);  

          Output:

          16

          Example 2: The following SELECT query uses LOCATE() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, LOCATE(' r ', Faculty_Address, 1) AS LOCATE_r_Address FROM Faculty_Info;  

          This SQL statement shows the index value of ‘r’ in the address of each faculty.

          Output:

          Faculty_AddressLOCATE_r_Address
          Aman Vihar10
          Nirman Vihar3
          Sector 1286
          Vivek Vihar11
          Sarvodya Calony3
          Krishna Nagar2

          LOWER String Function

          This string function allows users to convert the specified string into lower case letters. This function is also the same as the LCASE() string function.

          Syntax of LOWER String Function:

          Syntax1: This syntax uses LOWER() with the column of the SQL table:

          SELECT LOWER(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LOWER() with the string:

          SELECT LOWER(String);  

          Examples of LOWER String function:

          Example 1: The following SELECT query converts the upper case letters of the given string into the lower case letters.

          SELECT LOWER( 'NEW DELHI IS THE CAPITAL OF INDIA');  

          Output:

          new delhi is the capital of india

          Example 2: The following SELECT query uses LOWER() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, LOWER(Faculty_Address) AS LOWER_Address FROM Faculty_Info;  

          This SQL statement converts the cities of all faculties into lower case letters.

          Output:

          Faculty_AddressLOWER_Address
          Aman Viharaman vihar
          Nirman Viharnirman vihar
          Sector 128sector 128
          Vivek Viharvivek vihar
          Sarvodya Calonysarvodya colony
          Krishna Nagarkrishna nagar

          LPAD String Function

          This string function adds the given symbol to the left of the given string.

          Syntax of LPAD String Function:

          Syntax1: This syntax uses LPAD() with the column of the SQL table:

          SELECT LPAD(Column_Name, size, symbol) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LPAD() with the string:

          SELECT LPAD(String, size, symbol);  

          Examples of LPAD String function:

          Example 1: The following SELECT query adds the # symbol three times to the left of the NEW string:

          SELECT LPAD( 'NEW', 6, '#');  

          Output:

          ###NEW

          Example 2: The following SELECT query uses LPAD() with the Faculty_City column of the above Faculty_Info table:

          SELECT Faculty_City, LPAD(Faculty_City, 10, '*') AS LPAD_City FROM Faculty_Info;  

          This SQL statement adds the * (asterisk) symbol five times to the left of the city of all faculties:

          Output:

          Faculty_CityLPAD_City
          Delhi*****Delhi
          Delhi*****Delhi
          Mumbai****Mumbai
          Kolkata***Kolkata
          Delhi*****Delhi
          Lucknow***Lucknow

          LTRIM String Function

          This string function cuts the given character or string from the left of the given original string. It also removes the space from the left of the specified string.

          Syntax of LTRIM String Function:

          Syntax1: This syntax uses LTRIM() with the column of the SQL table:

          SELECT LTRIM(Column_Name, string) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses LTRIM() with the string:

          SELECT LTRIM(Original_String, trimmed_string );

          Examples of LTRIM String function:

          Example 1: The following SELECT query trims the NEW DELHI words from the specified string:

          SELECT LTRIM( 'NEW DELHI IS THE CAPITAL OF INDIA', 'NEW DELHI');  

          Output:

          IS THE CAPITAL OF INDIA

          Example 2: The following SELECT query trims the space from the specified string:

          SELECT LTRIM( '              JAVATPOINT           ');  

          Output:

          'JAVATPOINT           '

          Example 3: The following SELECT query trims the given character from the left of specified string:

          SELECT LTRIM( '####98221545', '#');

          Output:

          98221545

          Example 4: The following SELECT query uses LTRIM() with the Faculty_Last_Name column of above Faculty_Info table:

          SELECT Faculty_Last_Name, LTRIM(Faculty_Last_Name) AS LTRIM_LastName FROM Faculty_Info;  

          This SQL statement trims the space from the left of the last name of all faculties:

          Output:

          Faculty_Last_NameLTRIM_LastName
          SharmaSharma
          RoyRoy
          RoyRoy
          SinghaniaSinghania
          SharmaSharma
          BesasBesas

          MID String Function

          This string function extracts the sub-string from the given position of the original string.

          Syntax of MID String Function:

          Syntax1: This syntax uses MID() with the column of the SQL table:

          SELECT MID(Column_Name, Starting_Position, Length) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses MID() with the string:

          SELECT MID(Original_String, Starting_Position, Length);  

          Examples of MID String function:

          Example 1: The following SELECT query shows the character from the 5th to the 10th position of the string.

          SELECT MID( 'NEW DELHI IS THE CAPITAL OF INDIA', 5, 10);  

          Output:

          DELHI IS T

          Example 2: The following SELECT query uses MID() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, MID(Faculty_Address, 3, 8 ) AS MID_Address FROM Faculty_Info;  

          This SQL statement shows the character from the 3rd position till the 8th position of the address.

          Output:

          Faculty_AddressMID_Address
          Aman Viharan Vihar
          Nirman Viharrman Vih
          Sector 128ctor 128
          Vivek Viharvek Viha
          Sarvodya Calonyrvodya C
          Krishna Nagarishna Na

          POSITION String Function

          This string function finds the position of the first occurrence of the given string in the main string.

          Syntax of POSITION String Function:

          Syntax1: This syntax uses POSITION() with the column of the SQL table:

          SELECT POSITION(String IN Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses POSITION() with the string:

          SELECT POSITION(String IN Original_String);  

          Examples of POSITION String function:

          Example 1: The following SELECT query finds the position of the IT Company string in the original string:

          SELECT POSITION( 'IT Company' IN'javatpoint is an indian IT company');  

          Output:

          25

          Example 2: The following SELECT query finds the position of the ‘H’ string in the original string:

          SELECT POSITION( 'H' IN'HINDUSTAN');  

          Output:

          1

          Example 3: The following SELECT query uses POSITION() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, POSITION('a' IN Faculty_Address ) AS POSITION_a_IN Address FROM Faculty_Info;  

          This SQL statement finds the position of character ‘a’ in the address of each faculty:

          Output:

          Faculty_AddressPOSITION_a_IN Address
          Aman Vihar3
          Nirman Vihar5
          Sector 1280
          Vivek Vihar10
          Sarvodya Calony2
          Krishna Nagar7

          REPEAT String Function

          This string function writes the given string or character till the given number of times.

          Syntax of REPEAT String Function:

          Syntax1: This syntax uses REPEAT() with the column of the SQL table:

          SELECT REPEAT(Column_Name, Repetation_Number) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses REPEAT() with the string:

          SELECT REPEAT( String, Repetation_Number);  

          Examples of REPEAT String function:

          Example 1: The following SELECT query writes the given string three times in the output.

          SELECT REPEAT( 'javatpoint is an indian IT company', 3);

          Output:

          javatpoint is an indian IT companyjavatpoint is an indian IT companyjavatpoint is an indian IT company

          Example 2: The following SELECT query writes the given character five times in the output.

          SELECT REPEAT( 'H ' , 5);  

          Output:

          H H H H H

          Example 3: The following SELECT query uses REPEAT() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, REPEAT( Faculty_Address, 2 ) AS REPEAT_Address FROM Faculty_Info;  

          This SQL statement writes the address of each faulty two times in the Repeat_Address column.

          Output:

          Faculty_AddressREPEAT_Address
          Aman ViharAman ViharAman Vihar
          Nirman ViharNirman ViharNirman Vihar
          Sector 128Sector 128Sector 128
          Vivek ViharVivek ViharVivek Vihar
          Sarvodya CalonySarvodya CalonySarvodya Calony
          Krishna NagarKrishna NagarKrishna Nagar

          REPLACE String Function

          This string function cuts the given string by removing the given sub-string.

          Syntax of REPLACE String Function:

          Syntax1: This syntax uses REPLACE() with the column of the SQL table:

          SELECT REPLACE(Column_Name, sub_string) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses REPLACE() with the string:

          SELECT REPLACE( Original_String, sub_string);     

          Examples of REPLACE String function:

          Example 1: The following SELECT query removes the ‘javatpoint’ word from the original string:

          SELECT REPLACE( 'javatpoint Indian IT company javatpoint', 'javatpoint');  

          Output:

          Indian IT company

          Example 2: The following SELECT query removes the given character H from the string:

          SELECT REPLACE( 'HIJHKHJKL' , 'H');  

          Output:

          IJKJKL

          Example 3: The following SELECT query uses REPLACE() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, REPLACE( Faculty_Address, 'a' ) AS REPLACE_a_Address FROM Faculty_Info;  

          This SQL statement removes the character a from the address of each faulty:

          Output:

          Faculty_AddressREPLACE_a_Address
          Aman ViharAmn Vihr
          Nirman ViharNirmn Vihr
          Sector 128Sector 128
          Vivek ViharVivek Vihr
          Sarvodya CalonySrvody Clony
          Krishna NagarKrishn Ngr

          REVERSE String Function

          This string function of Structured query Language reverses all the characters of the string.

          Syntax of REVERSE String Function:

          Syntax1: This syntax uses REVERSE() with the column of the SQL table:

          SELECT REVERSE(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses REVERSE() with the string:

          SELECT REVERSE(String);  

          Examples of REVERSE String function:

          Example 1: The following SELECT query reverses the characters of the JavaTpoint string:

          SELECT REVERSE( 'javatpoint');  

          Output:

          tnioptavaj

          Example 3: The following SELECT query uses REVERSE() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, REVERSE( Faculty_Address ) AS REVERSE_Address FROM Faculty_Info;  

          This SQL statement reverses the address of each faculty:

          Output:

          Faculty_AddressREVERSE_Address
          Aman Viharrahiv nama
          Nirman Viharrahiv namrin
          Sector 128821 rotces
          Vivek Viharrahiv keviv
          Sarvodya Calonyynolac aydovras
          Krishna Nagarragan anhsirk

          RIGHT String Function

          This string function shows the right-most characters from the given string. It reads the characters from the right side to the given index position.

          Syntax of RIGHT String Function:

          Syntax1: This syntax uses RIGHT() with the column of the SQL table:

          SELECT RIGHT(Column_Name, Index_position) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses RIGHT() with the string:

          SELECT RIGHT(String, Index_position);  

          Examples of RIGHT String function:

          Example 1: The following SELECT query shows the 11 right-most characters from the given string:

          SELECT RIGHT( 'The CAPITAL of INDIA is NEW DELHI', 11);  

          Output:

          s NEW DELHI

          Example 2: The following SELECT query uses RIGHT() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, RIGHT(Faculty_Address, 7) AS RIGHT_Address FROM Faculty_Info;  

          This SQL statement shows the 7 right-most characters from the address of each faculty.

          Output:

          Faculty_AddressRIGHT_Address
          Aman Viharn Vihar
          Nirman Viharn Vihar
          Sector 128tor 128
          Vivek Vihark Vihar
          Sarvodya CalonyCalony
          Krishna Nagara Nagar

          RPAD String Function

          This string function adds the given symbol to the right of the given string.

          Syntax of RPAD String Function:

          Syntax1: This syntax uses RPAD() with the column of the SQL table:

          SELECT RPAD(Column_Name, size, symbol) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses RPAD() with the string:

          SELECT RPAD(String, size, symbol);  

          Examples of RPAD String function:

          Example 1: The following SELECT query adds the # symbol three times to the right of the NEW string:

          SELECT RPAD( 'NEW', 6, '#');  

          Output:

          NEW###

          Example 2: The following SELECT query uses RPAD() with the Faculty_City column of the above Faculty_Info table:

          SELECT Faculty_City, RPAD(Faculty_City, 10, '*') AS RPAD_City FROM Faculty_Info;  

          This SQL statement adds the * (asterisk) symbol to the right of the city of each faculty.

          Output:

          Faculty_CityRPAD_City
          DelhiDelhi*****
          DelhiDelhi*****
          MumbaiMumbai****
          KolkataKolkata***
          DelhiDelhi*****
          LucknowLucknow***

          RTRIM String Function

          This string function cuts the given character or string from the right of the given original string. It also removes the space from the right of the specified string.

          Syntax of RTRIM String Function:

          Syntax1: This syntax uses RTRIM() with the column of the SQL table:

          SELECT RTRIM(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses RTRIM() with the string:

          SELECT RTRIM(Original_String);  

          Examples of RTRIM String function:

          Example 1: The following SELECT query trims the NEW DELHI words from the specified string:

          SELECT RTRIM( 'NEW DELHI IS THE CAPITAL OF INDIA', 'CAPITAL OF INDIA');  

          Output:

          NEW DELHI IS THE

          Example 2: The following SELECT query trims the space from the right of the specified string:

          SELECT RTRIM( '              JAVATPOINT           ');  

          Output:

          '              JAVATPOINT'

          Example 3: The following SELECT query trims the given character from the right of the specified string:

          SELECT RTRIM( '98221545####', '#');  

          Output:

          98221545

          Example 4: The following SELECT query uses RTRIM() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, RTRIM(Faculty_Address) AS rtrimaddress FROM Faculty_Info;  

          This SQL statement trims the space from the right of the address of each faculty:

          Output:

          Faculty_Addressrtrimaddress
          Aman ViharAman Vihar
          Nirman ViharNirman Vihar
          Sector 128Sector 128
          Vivek ViharVivek Vihar
          Sarvodya CalonySarvodya Calony
          Krishna NagarKrishna Nagar

          SPACE String Function

          This string function adds the specified number of spaces.

          Syntax of SPACE String Function:

          SELECT SPACE(Number);  

          Example of SPACE String function:

          The following SELECT query adds the 11 spaces:

          Output:

          ___________

          STRCMP String Function

          This string function compares the two specified strings with each other. This function returns 0 if both strings in SQL are similar, returns -1 if the first string is smaller than the second string, and returns 1 if the first string is bigger than the second string.

          Syntax of STRCMP String Function:

          Syntax1: This syntax uses STRCMP() with the columns of the SQL table:

          SELECT STRCMP(Column_Name1, Column_Name2) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses STRCMP() with the two strings:

          SELECT STRCMP(String1, String2);  

          Examples of STRCMP String function:

          Example 1: The following SELECT query compares the ‘INDIA’ string with the ‘JavaTpoint’ string.

          SELECT STRCMP( 'INDIA, 'JavaTpoint');  

          Output:

          -1

          Example 2: The following SELECT query compares the ‘INDIA’ string with the ‘Point’ string.

          SELECT STRCMP( 'INDIA, 'Point');  

          Output:

          0

          Example 3: The following SELECT query uses STRCMP() with the Faculty_first_Name and Faculty_Last_Name columns of the above Faculty_Info table:

          SELECT Faculty_First_Name, Faculty_Last_Name, STRCMP(Faculty_First_Name, Faculty_Last_Name) AS STRCMP_Name FROM Faculty_Info;  

          This SQL statement compares the first name and last name of each faculty.

          Output:

          Faculty_First_NameFaculty_Last_NameSTRCMP_Name
          ArushSharma-1
          BulbulRoy1
          SaurabhRoy1
          ShivaniSinghania-1
          AvinashSharma1
          ShyamBesas0

          SUBSTR String Function

          This string function extracts the sub-string from the given position of the original string.

          Syntax of SUBSTR String Function:

          Syntax1: This syntax uses SUBSTR() with the column of the SQL table:

          SELECT SUBSTR(Column_Name, Starting_Position, Length) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses SUBSTR() with the string:

          SELECT SUBSTR(Original_String, Starting_Position, Length);  

          Examples of SUBSTR String function:

          Example 1: The following SELECT query shows the character from the 5th to the 10th position of the string.

          SELECT SUBSTR( 'NEW DELHI IS THE CAPITAL OF INDIA', 5, 10);  

          Output:

          DELHI IS T

          Example 2: The following SELECT query uses SUBSTR() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, SUBSTR(Faculty_Address, 3, 8 ) AS SUBSTR_Address FROM Faculty_Info;

          This SQL statement shows the substring from the 3rd position to the 8th position of the address.

          Output:

          Faculty_AddressSUBSTR_Address
          Aman Viharan Vihar
          Nirman Viharrman Vih
          Sector 128ctor 128
          Vivek Viharvek Viha
          Sarvodya Calonyrvodya C
          Krishna Nagarishna Na

          SUBSTRING String Function

          This string function shows the character of the given index value in the original string.

          Syntax of SUBSTRING String Function:

          Syntax1: This syntax uses SUBSTRING() with the column of the SQL table:

          SELECT SUBSTRING(Column_Name, Index_Position, Starting_Position) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses SUBSTRING() with the string:

          SELECT SUBSTRING(Original_String, Index_Position, Starting_Position);

          Examples of SUBSTRING String function:

          Example 1: The following SELECT query shows the character of the fifth position from the left side.

          SELECT SUBSTRING('NEW DELHI IS THE CAPITAL OF INDIA', 5, 1);

          Output:

          D

          Example 2: The following SELECT query uses SUBSTRING() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, SUBSTRING(Faculty_Address, 3, 1 ) AS SUBSTRING_Address FROM Faculty_Info;  

          This SQL statement shows the character of the 3rd position from the left side of the address of each faculty.

          Output:

          Faculty_AddressSUBSTRING_Address
          Aman Vihara
          Nirman Viharr
          Sector 128c
          Vivek Viharv
          Sarvodya Calonyr
          Krishna Nagari

          SUBSTRING_INDEX String Function

          This string function shows the substring before the given symbol in the original string.

          Syntax of SUBSTRING_INDEX String Function:

          This syntax uses SUBSTRING_INDEX() with the string:

          SELECT SUBSTRING_INDEX(Original_String, symbol, Starting_Position);  

          Example of SUBSTRING_INDEX String function:

          The following SELECT query shows the substring before the @ symbol:

          SELECT SUBSTRING_INDEX( 'NEW DELHI@IS THE CAPITAL OF INDIA', @, 1);  

          Output:

          NEW DELHI

          UCASE String Function

          This string function allows users to convert the specified string into upper case letters or capital letters.

          Syntax of UCASE String Function:

          Syntax1: This syntax uses UCASE() with the column of the SQL table:

          SELECT UCASE(Column_Name) AS Alias_Name FROM Table_Name;  

          Syntax2: This syntax uses UCASE() with the string:

          SELECT UCASE(String);  

          Examples of UCASE String function:

          Example 1: The following SELECT query converts the lower case letters of a given string into the upper case letters.

          SELECT UCASE( 'The CAPITAL of INDIA is NEW DELHI');  

          Output:

          THE CAPITAL OF INDIA IS NEW DELHI'

          Example 1: The following SELECT query converts the given small letter into a capital letter:

          SELECT UCASE( 'e');  

          Output:

          E

          Example 2: The following SELECT query uses UCASE() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, UCASE(Faculty_Address) AS UCASE_City FROM Faculty_Info;  

          This SQL statement converts the cities of all faculties into capital letters.

          Output:

          Faculty_AddressUCASE_Address
          Aman ViharAMAN VIHAR
          Nirman ViharNIRMAN VIHAR
          Sector 128SECTOR 128
          Vivek ViharVIVEK VIHAR
          Sarvodya CalonySARVODYA CALONY
          Krishna NagarKRISHNA NAGAR

          UPPER String Function

          This string function allows users to convert the specified string into the UPPER case letters. This function is also the same as the UCASE() string function.

          Syntax of UPPER String Function:

          Syntax1: This syntax uses UPPER() with the column of the SQL table:

          SELECT UPPER(Column_Name) AS Alias_Name FROM Table_Name;

          Syntax2: This syntax uses UPPER() with the string:

          SELECT UPPER(String);  

          Examples of UPPER String function:

          Example 1: The following SELECT query converts the LOWER caseletters of the given string into the UPPER case letters.

          SELECT UPPER( 'new delhi is the capital of India');  

          Output:

          NEW DELHI IS THE CAPITAL OF INDIA

          Example 2: The following SELECT query uses UPPER() with the Faculty_Address column of the above Faculty_Info table:

          SELECT Faculty_Address, UPPER(Faculty_Address) AS UPPER_Address FROM Faculty_Info;  

          This SQL statement converts the cities of all faculties into the UPPER case letters.

          Output:

          Faculty_AddressUPPER_Address
          Aman ViharAMAN VIHAR
          Nirman ViharNIRMAN VIHAR
          Sector 128SECTOR 128
          Vivek ViharVIVEK VIHAR
          Sarvodya CalonySARVODYA CALONY
          Krishna NagarKRISHNA NAGAR

          Comments

          Leave a Reply

          Your email address will not be published. Required fields are marked *