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. Cutting blanks off a string value for display. You can use 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 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 word in a string.

Examples of String Functions in SQL

String function is easy to use. Here we will discuss how to use string function in SQL programming with the help of examples

ASCII(): It gives you the ASCII value of a character.

Example:

SELECT ASCII('t');

Result: 116

CHAR_LENGTH(): It gives you the number of characters in the string.

Example:

SELECT CHAR_LENGTH('world!');

Result: 6

CHARACTER_LENGTH(): It gives you the number of characters in a given set of strings.

Example:

SELECT CHARACTER_LENGTH( 'educba.com' );

Result: 10

CONCAT(): It appends two strings to create the new single string as shown in the example.

Example:

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

Result: ‘educba.com’

CONCAT_WS(): It appends two string with a given symbol in between to concatenate them as shown in the example.

Example:

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

Result: educba_to_learn

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

Example:

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

Result: 3

FORMAT(): It changes the format of the text from a string to any other format.

Example:

FORMAT("0.254", "Percent");

Result: ‘25.40%’

INSERT(): It helps you insert text, integer, float or double into your database.

Example:

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

Result: inserted successfully

INSTR(): It gives you the first occurrence of the index of a character in the string.

Example:

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

Result: 1 (the first occurrence of ‘e’)

LCASE(): It will replace every character in the string in their lowercase letter.

Example:

LCASE("eduCBA.com To Learn");

Result: educba.com to learn

LEFT(): It is used to get the substring from the left of the string till the given index position.

Example:

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

Result: edu

LENGTH(): gives you the length of the string.

Example:

LENGTH('educba.com');

Result: 10

LOCATE(): It gives you the position of a substring in the given string.

Example:

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

Result: 4

LOWER(): It converts every character in a string to lowercase from uppercase.

Example:

SELECT LOWER('EDUCBA.COM');

Result: educba.com

LPAD(): It adds left padding with the given symbol to make the string of given size.

Example:

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

Result:@@yahoo

LTRIM(): It trims the given character from the left of the string.

Example:

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

Result: yahoo

MID(): It gives you the substring from given position till the number of character in the string.

Example:

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

Result: cba

POSITION(): It gives you the index position of the character in the string.

Example:

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

Result: 3

REPEAT(): It repeats the string to the number of times given.

Example:

SELECT REPEAT('educba', 2);

Result: educbaeducba

REPLACE(): It returns new string by removing given string from the original.

Example:

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

Result: yahoo

REVERSE(): It reverses the characters in a string.

Example:

SELECT REVERSE('educba.com');

Result: moc.abcuda

RIGHT(): It is used to get the substring from the right of the string till the given index.

Example:

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

Result: ‘.com’

RPAD(): It adds right padding with the given symbol to make the string of given size.

Example:

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

Result: ‘educba@@’

RTRIM(): It trims the given character from the right of the string.

Example:

RTRIM('educbapou', 'pou');

Result: ‘educba’

SPACE(): It adds the number of spaces specified.

Example:

SELECT SPACE(6);

Result: ‘ ‘

STRCMP(): matches two strings.
- If both the strings are the same it gives 0.
- If first is smaller than second than it gives -1.
- If first is bigger than second than it gives 1.

Example:

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

Result: 0

SUBSTR(): It returns a new substring from given position till the number of character.

Example:

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

Result: ‘educba’

SUBSTRING(): It gives you the substring character from the given string.

Example:

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

Result: ‘C’

SUBSTRING_INDEX(): It gives you the substring until it finds the given symbol.

Example:

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

Result: ‘https:’

TRIM(): It trims the given character from the string.

Example:

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

Result: 567

UCASE(): It will replace every character in the string in their uppercase letter.

Example:

UCASE("EduCbA");

Result: EDUCBA

Conclusion: SQL String Functions

In simplest mean it the user-defined function (UDF) in SQL server programming to help the user by accepting users input parameter 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 length of string by adding specific character at end or starting, STRCMP string, compare two strings, get the type format of input string, getting the desired results from the given set of text, finding the specific format of text from the string.