SQL | String functions

String functions
are used to perform an operation on input string and return an output string.
Following are the string functions defined in SQL:

1. ASCII(): This function is used to find the ASCII value of a character.

   Syntax: SELECT ascii('t');
   Output: 116

2. CHAR_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL Server. This function is used to find the length of a word.

   Syntax: SELECT char_length('Hello!');
   Output: 6

3. CHARACTER_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL Server. This function is used to find the length of a line.

   Syntax: SELECT CHARACTER_LENGTH('geeks for geeks');
   Output: 15

4. CONCAT(): This function is used to add two words or strings.

   Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
   Output: ‘GeeksforGeeks’

5. CONCAT_WS(): This function is used to add two words or strings with a symbol as concatenating symbol.

   Syntax: SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');
   Output: geeks_for_geeks

6. FIND_IN_SET(): This function is used to find a symbol from a set of symbols.

   Syntax: SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');
   Output: 2

7. FORMAT(): This function is used to display a number in the given format.

   Syntax: Format("0.981", "Percent");
   Output: ‘98.10%’

8. INSERT(): This function is used to insert the data into a database.

   Syntax: INSERT INTO database (geek_id, geek_name) VALUES (5000, 'abc');
   Output: successfully updated

9. INSTR(): This function is used to find the occurrence of an alphabet.

   Syntax: INSTR('geeks for geeks', 'e');
   Output: 2 (the first occurrence of ‘e’)

   Syntax: INSTR('geeks for geeks', 'e', 1, 2 );
   Output: 3 (the second occurrence of ‘e’)

10. LCASE(): This function is used to convert the given string into lower case.

    Syntax: LCASE ("GeeksFor Geeks To Learn");
    Output: geeksforgeeks to learn

11. LEFT(): This function is used to SELECT a sub string from the left of given size or characters.

    Syntax: SELECT LEFT('geeksforgeeks.org', 5);
    Output: geeks

12. LENGTH(): This function is used to find the length of a word.

    Syntax: LENGTH('GeeksForGeeks');
    Output: 13

13. LOCATE(): This function is used to find the nth position of the given word in a string.

    Syntax: SELECT LOCATE('for', 'geeksforgeeks', 1);
    Output: 6

14. LOWER(): This function is used to convert the upper case string into lower case.

    Syntax: SELECT LOWER('GEEKSFORGEEKS.ORG');
    Output: geeksforgeeks.org

15. LPAD(): This function is used to make the given string of the given size by adding the given symbol.

    Syntax: LPAD('geeks', 8, '0');
    Output:
    000geeks

16. LTRIM(): This function is used to cut the given sub string from the original string.

    Syntax: LTRIM('123123geeks', '123');
    Output: geeks

17. MID(): This function is to find a word from the given position and of the given size.

    Syntax: Mid ("geeksforgeeks", 6, 2);
    Output: for

18. POSITION(): This function is used to find position of the first occurrence of the given alphabet.

    Syntax: SELECT POSITION('e' IN 'geeksforgeeks');
    Output: 2

19. REPEAT(): This function is used to write the given string again and again till the number of times mentioned.

    Syntax: SELECT REPEAT('geeks', 2);
    Output: geeksgeeks

20. REPLACE(): This function is used to cut the given string by removing the given sub string.

    Syntax: REPLACE('123geeks123', '123');
    Output: geeks

21. REVERSE(): This function is used to reverse a string.

    Syntax: SELECT REVERSE('geeksforgeeks.org');
    Output: ‘gro.skeegrofskeeg’

22. RIGHT(): This function is used to SELECT a sub string from the right end of the given size.

    Syntax: SELECT RIGHT('geeksforgeeks.org', 4);
    Output: ‘.org’

23. RPAD(): This function is used to make the given string as long as the given size by adding the given symbol on the right.

    Syntax: RPAD('geeks', 8, '0');
    Output: ‘geeks000’

24. RTRIM(): This function is used to cut the given sub string from the original string.

    Syntax: RTRIM('geeksxyxzyyy', 'xyz');
    Output: ‘geeks’

25. SPACE(): This function is used to write the given number of spaces.

    Syntax: SELECT SPACE(7);
    Output: ‘       ‘

26. STRCMP(): This function is used to compare 2 strings.
    • If string1 and string2 are the same, the STRCMP function will return 0.
    • If string1 is smaller than string2, the STRCMP function will return -1.
    • If string1 is larger than string2, the STRCMP function will return 1.

    Syntax: SELECT STRCMP('google.com', 'geeksforgeeks.com');
    Output: -1

27. SUBSTR(): This function is used to find a sub string from the a string from the given position.

    Syntax:SUBSTR('geeksforgeeks', 1, 5);
    Output: ‘geeks’

28. SUBSTRING(): This function is used to find an alphabet from the mentioned size and the given string.

    Syntax: SELECT SUBSTRING('GeeksForGeeks.org', 9, 1);
    Output: ‘G’

29. SUBSTRING_INDEX(): This function is used to find a sub string before the given symbol.

    Syntax: SELECT SUBSTRING_INDEX('www.geeksforgeeks.org', '.', 1);
    Output: ‘www’

30. TRIM(): This function is used to cut the given symbol from the string.

    Syntax: TRIM(LEADING '0' FROM '000123');
    Output: 123

31. UCASE(): This function is used to make the string in upper case.

    Syntax: UCASE ("GeeksForGeeks");
    Output:
    GEEKSFORGEEKS