EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more