EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL String Functions

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL String Functions

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.

Examples of String Functions in SQL

The string function is easy to use.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,900 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

This has been a guide to SQL String Functions. Here we discussed how to use string function in SQL programming with the help of examples. You can also go through our other suggested articles to learn more–

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

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

4 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ADD Column
    • SQL Ranking Function
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

EDUCBA

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

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

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More