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(): This function is used to find the length of a word.
    Syntax: SELECT char_length('Hello!');
    Output: 6
  3. CHARACTER_LENGTH(): 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


My Personal Notes arrow_drop_up

I like to do coding in C++C and java programming languages HTML and CSS always intersts me Sharing knowleged is the best way according to me to increase ones knwoledge

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.