Open In App

SQL | String functions

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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

Last Updated : 30 Dec, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads