SQL String Functions: The Complete Guide

0
44
SQL String Functions Example | String Functions In SQL

SQL String Functions

SQL String functions are used for manipulating the strings. Here, in SQL, we provide the string as input, and then after applying string functions desired manipulated string is obtained. 

See the following String Functions.

ASCII()

SQL ASCII function is used for finding the ascii value of the character, i.e., the numeric value. If more than one character is inserted within the function, the leftmost character value will be displayed. Here, we are using SQL SELECT Statement.

Syntax

SELECT ASCII('ab');

Output

97

CHAR_LENGTH()

The char_length() function is used to find the word’s length.

Syntax

SELECT char_length('Shubh');

#Output

5

CHARACTER_LENGTH()

The character_length() function is used to return the entire string’s length.

Syntax

SELECT CHARACTER_LENGTH ('God Is Great');

Output

12

CONCAT()

SQL concat() function is used for adding two words or strings. It may have one or more arguments, and if all arguments are non-binary strings, then the result is a non-binary string.

SYNTAX

SELECT CONCAT ('My', 'S', 'QL');

OUTPUT:

MySQL

CONCAT_WS()

SQL concat_ws() is used to add two words or strings with a symbol as a concatenating symbol.

Syntax

SELECT CONCAT_WS ('_', 'God', 'is', 'Great');

Output

God_is_Great

FIND_IN_SET()

The find_in_set() function is used for finding a symbol from a set of symbols. This mainly returns the position in which the symbol is present.

Syntax

SELECT FIND_IN_SET ('b', 'a, b, c, d, e, f');

Output

2

FORMAT() 

SQL format() function is used for displaying a number in the given format.

Syntax

SELECT Format ("0.981", "Percent");

Output

‘98.10%’

INSERT()

SQL insert() function is used for inserting the data into a database.

SYNTAX:

INSERT INTO database (Id, Name) VALUES (1, 'Shubh');

INSTR()

The instr() function is used for finding the occurrence of an alphabet. It generally displays the first occurrence of the string.

Syntax

SELECT INSTR ('God is Great', 'e');

Output

11

LCASE(): 

The lcase() function converts the given string into a lower case.

Syntax:

SELECT LCASE ("Welcome to Appdividend”);

Output

Welcome to appdividend

LEFT()

The left() function selects a substring from the left of a given size or characters.

Syntax

SELECT LEFT ('Appdividend.com', 5);

Output

Appdi

LENGTH()

The length() function is used for finding the length of a word.

#Syntax:

SELECT LENGTH('Shubh');

#Output

13

LOCATE()

The locate() function is used for finding the nth position of a given the word in the string.

Syntax

SELECT LOCATE ('is', 'GodisGreat', 1);

Output

4

Lower()

The lower() function converts the upper-case string into lower case.

Syntax

SELECT LOWER ('APPDIVIDEND.COM');

Output

appdividend.com

LPAD()

The lpad() function returns the string argument, left-padded with the specified string.

Syntax

SELECT LPAD ('Shubh', 8, '0');

Output

000Shubh

LTRIM()

The ltrim() function is used for cutting the given substring from the original string.

Syntax

SELECT LTRIM ('44App', '44');

Output

App

MID()

The mid() function returns a substring starting from the specified position.

Syntax

Select Mid ("LoveisLife", 6, 2);

Output

sLi

POSITION()

The position() function is used for finding the position of the first occurrence of a given alphabet.

Syntax

SELECT POSITION ('e' IN 'AppDividend');

Output

9

REPEAT() 

The repeat() function is used to write the given string again and again until the number of times mentioned.

Syntax

SELECT REPEAT ('SQL', 2);

Output

SQLSQL

REPLACE()

The replace() function is used for cutting the given string in a function by removing the given substring.

Syntax

SELECT REPLACE ('123App123', '123');

Output

App

REVERSE()

The reverse() function reverses a string specified inside a function.

Syntax

SELECT REVERSE('Shubh');

Output

hbuhs

RIGHT()

The right() function selects a substring from the right end of the given size.

Syntax

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

Output

.com

RPAD()

The rpad() function is used for making a given string as long as the given size by adding the given symbol on the right.

Syntax

SELECT RPAD ('Shubh', 8, '0');

Output

Shubh000

RTRIM()

The rtrim() function is used for cutting a part of the string from the original string.

Syntax

SELECT RTRIM ('Shubhxyz', 'xyz');

Output

Shubh

SPACE() 

The space() function is used to write a given number of spaces.

Syntax

SELECT SPACE(7);

Output’ ‘

STRCMP() 

The strcmp() function is used to compare the two strings.

    1. If string1 and string2 are the same, the STRCMP function will return 0.
    2. If string1 is smaller than string2, the STRCMP function will return -1.
    3. If string1 is larger than string2, the STRCMP function will return 1.

Syntax

SELECT STRCMP ('google.com', 'AppDividend.com');

Output

1

SUBSTR()

The substr() function is used for finding a substring from the given string and its position specified within the function.

Syntax

SELECT SUBSTR ('GodisGreat', 1, 5);

Output

Godis

SUBSTRING()

The substring() function is used for finding an alphabet from the mentioned size and the given string.

Syntax

SELECT SUBSTRING ('AppDividend.com', 9, 1);

Output

e

SUBSTRING_INDEX()

The substring_index() function is used for finding a substring before the given symbol specified within a function.

Syntax

SELECT SUBSTRING_INDEX ('www.AppDivivdend.com', '.', 1);

Output

www

SOUNDEX (str):

The soundex() function is used for returning the Soundex string.

A Soundex string is four characters long, but the function returns an arbitrarily long string. We can use the SUBSTRING() function to get a standard Soundex string.

All non-alphabetic characters in str are ignored, and all international alphabetic characters outside the A-Z range are treated as vowels.

SYNTAX

SELECT SOUNDEX('Hello');

OUTPUT

H400

TRIM() 

The trim() function is used for cutting the given symbol from the string mentioned.

Syntax

SELECT TRIM (LEADING '0' FROM '000123');

Output

123

UCASE() 

The ucase() function is used for making the given string into the upper case.

Syntax

SELECT UCASE ("Appdividend");

Output

APPDIVIDEND

UNHEX (str)

It is used for converting given hexadecimal characters to digits or ascii characters.

Syntax

SELECT UNHEX('4D7953514C');

Output

SQL

That’s it for this tutorial.

Leave A Reply

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.