EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL String Functions
 

SQL String Functions

Priya Pedamkar
Article byPriya Pedamkar

Updated March 18, 2023

SQL String Functions

 

 

Introduction to SQL String Functions

SQL string functions are primarily utilized for string manipulation. The built-in SQL String functions make it easier for us to find and alter string values. For example, cutting blanks off a string value for display. You can use the LEN function to find the length of a string. It takes a single parameter containing a string expression, concatenating two strings. You can find the given the word from the sentence; even you can substring the character up to the given point in the string. You can find a word from the given point and of the given length using the MID function. You can also find the nth position of the given the word in a string.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Examples of String Functions in SQL

The string function is easy to use.

Here we will see how to use string function in SQL programming with the help of examples:

1. ASCII()

It gives you the ASCII value of a character.

Example:

Code:

SELECT ASCII('t');

Output:

116

2. CHAR_LENGTH()

It gives you the number of characters in the string.

Example:

Code:

SELECT CHAR_LENGTH('world!');

Output:

6

3. CHARACTER_LENGTH()

It gives you the number of characters in a given set of strings.

Example:

Code:

SELECT CHARACTER_LENGTH( 'educba.com' );

Output:

10

4. CONCAT()

It appends two strings to create the new single string, as shown in the example.

Example:

Code:

SELECT 'educba' || ' ' || '.com' FROM viber;

Output:

‘educba.com’

5. CONCAT_WS()

It appends two strings with a given symbol in between to concatenate them, as shown in the example.

Example:

Code:

SELECT CONCAT_WS('_', 'educba', 'to', 'learn');

Output:

educba_to_learn

6. FIND_IN_SET()

It finds out the index position of any symbol or character from the given set of characteristics.

Example:

Code:

SELECT FIND_IN_SET('v', 'z, x, v, b, n, m');

Output:

3

7. FORMAT()

It changes the format of the text from a string to any other format.

Example:

Code:

FORMAT("0.254", "Percent");

Output:

‘25.40%’

8. INSERT()

It helps you insert text, integer, float, or double into your database.

Example:

Code:

INSERT INTO mydb (name, age) VALUES (sdf, 20);

Output:

Inserted successfully

9. INSTR()

It gives you the first occurrence of the index of a character in the string.

Example:

Code:

INSTR('educba to learn', 'e');

Output:

1 (the first occurrence of ‘e’)

10. LCASE()

It will replace every character in the string in their lowercase letter.

Example:

Code:

LCASE("eduCBA.com To Learn");

Output:

educba.com to learn

11. LEFT()

It is used to get the substring from the left of the string to the given index position.

Example:

Code:

SELECT LEFT('educba.com', 3);

Output:

edu

12 LENGTH()

Gives you the length of the string.

Example:

Code:

LENGTH('educba.com');

Output:

10

13. LOCATE()

It gives you the position of a substring in the given string.

Example:

Code:

SELECT LOCATE('cba', 'educba.com', 1);

Output:

4

14. LOWER()

It converts every character in a string to lowercase from uppercase.

Code:

SELECT LOWER('EDUCBA.COM');

Output:

educba.com

15. LPAD()

It adds left padding with the given symbol to make the string of a given size.

Example:

Code:

LPAD('yahoo', 7, '@');

Output:

@@yahoo

16. LTRIM()

It trims the given character from the left of the string.

Example:

Code:

LTRIM('345yahoo', '345');

Output:

yahoo

17. MID()

It gives you the substring from a given position to the number of characters in the string.

Example:

Code:

MID("educba.com", 3, 2);

Output:

cba

18. POSITION()

It gives you the index position of the character in the string.

Example:

Code:

SELECT POSITION('u' IN 'educba.com');

Output:

3

19. REPEAT()

It repeats the string to the number of times given.

Example:

Code:

SELECT REPEAT('educba', 2);

Output:

educbaeducba

20. REPLACE()

It returns a new string by removing the given string from the original.

Example:

Code:

REPLACE('456yahoo456', '456');

Output:

yahoo

21. REVERSE()

It reverses the characters in a string.

Example:

Code:

SELECT REVERSE('educba.com');

Output:

moc.abcuda

22. RIGHT()

It is used to get the substring from the right of the string to the given index.

Example:

Code:

SELECT RIGHT('educba.com', 4);

Output:

‘.com’

23. RPAD()

It adds the right padding with the given symbol to make the string of the given size.

Example:

Code:

RPAD('educba', 7, '2');

Output:

‘educba@@’

24. RTRIM()

It trims the given character from the right of the string.

Example:

Code:

RTRIM('educbapou', 'pou');

Output:

‘educba’

25. SPACE()

It adds the number of spaces specified.

Example:

Code:

SELECT SPACE(6);

Output:

‘      ‘

26. STRCMP()

Matches two strings.

    • If both the strings are the same, it gives 0.
    • If the first is smaller than the second, then it gives -1.
    • If the first is bigger than the second, then it gives 1.

Example:

Code:

SELECT STRCMP('zomato.com', 'educba.com');

Output:

0

27. SUBSTR()

It returns a new substring from a given position to the number of characters.

Example:

Code:

SUBSTR('educba.com', 1, 5);

Output:

‘educba’

28. SUBSTRING()

It gives you the substring character from the given string.

Example:

Code:

SELECT SUBSTRING('eduCba.com', 4, 1);

Output:

‘C’

29. SUBSTRING_INDEX()

It gives you the substring until it finds the given symbol.

Example:

Code:

SELECT SUBSTRING_INDEX( 'https://www.educba.com' , '/', 1);

Output:

‘https:’

30. TRIM()

It trims the given character from the string.

Example:

Code:

TRIM(LEADING '333' FROM '333567');

Output:

567

31. UCASE()

It will replace every character in the string in their uppercase letter.

Example:

Code:

UCASE("EduCbA");

Output:

EDUCBA

Conclusion

In the simplest mean, it is the user-defined function (UDF) in SQL server programming to help the user by accepting users’ input parameters and return the desired result as needed for them. You can send text field values as parameters in UDFs directly to manipulate the string. SQL string function is very useful. You can use SQL string functions to manipulate input string to concatenate, make lowercase, uppercase, increase the length of the string by adding specific character at end or starting, STRCMP string, compare two strings, get the type format of the input string, getting the desired results from the given set of text, finding the specific format of text from the string.

Recommended Articles

We hope that this EDUCBA information on “sql string functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. What is SQL
  2. How to Install SQL Server
  3. SQL Commands Update
  4. MySQL Grant

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW