SQL Strings

Please include the below MySQL functions in syllabus with example and explanations when you
will be delivering the MySQL topics –

UCASE Converts a string to upper-case

INSTR Returns the position of the first occurrence of a string in another string
STRCMP Compares two strings
Return Values
If string1 = string2, this function returns 0
If string1 < string2, this function returns -1
If string1 > string2, this function returns 1

SUBSTR Extracts a substring from a string (starting at any position)

NULLIF Compares two expressions and returns NULL if they are equal. Otherwise, the
first expression is returned
CURDATE Returns the current date
USER Returns the current MySQL user name and host name
VERSION Returns the current version of the MySQL database

1) UCASE Converts a string to upper-case

SELECT UPPER('SQL Tutorial is FUN!');

The UPPER() function converts a string to upper-case.
Syntax - UPPER(text)
Parameter values - text Required. The string to convert

Example Convert the text in "CustomerName" to upper-case:

SELECT UPPER(CustomerName) AS UppercaseCustomerName

FROM Customers;

2) INSTR Returns the position of the first occurrence of a string in another string

Example - Search for "3" in string "W3Schools.com", and return position:

SELECT INSTR("W3Schools.com", "3") AS MatchPosition;

The INSTR() function returns the position of the first occurrence of a string in another string.

This function performs a case-insensitive search.

Syntax - INSTR(string1, string2)

Parameter Values - string1 Required. The string that will be searched

string2 Required. The string to search for in string1. If string2 is not found, this function returns 0

Example

Search for "COM" in string "W3Schools.com", and return position:

SELECT INSTR("W3Schools.com", "COM") AS MatchPosition;

Search for "a" in CustomerName column, and return position:

SELECT INSTR(CustomerName, "a")

FROM Customers;

3) STRCMP Compares two strings

Return Values
If string1 = string2, this function returns 0
If string1 < string2, this function returns -1
If string1 > string2, this function returns 1

Example - Compare two strings:

SELECT STRCMP("SQL Tutorial", "SQL Tutorial");

The STRCMP() function compares two strings.
Syntax - STRCMP(string1, string2)

Parameter Values

Parameter Description

string1, string2 Required. The two strings to be compared

Return Values

• If string1 = string2, this function returns 0

• If string1 < string2, this function returns -1
• If string1 > string2, this function returns 1
• Example
• Compare two strings:
• SELECT STRCMP("SQL Tutorial", "HTML Tutorial");

4) SUBSTR Extracts a substring from a string (starting at any position)

Example Extract 3 characters from a string, starting in position 1:

SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;

Definition and Usage

The SUBSTRING() function extracts some characters from a string.

Syntax

SUBSTRING(string, start, length)

Parameter Values

Parameter Description

string Required. The string to extract from

start Required. The start position. The first position in string is 1

length Required. The number of characters to extract. Must be a positive number

Example

Extract 5 characters from the "CustomerName" column, starting in position 1:

SELECT SUBSTRING(CustomerName, 1, 5) AS ExtractString

FROM Customers;

Example

Extract 100 characters from a string, starting in position 1:

SELECT SUBSTRING('SQL Tutorial', 1, 100) AS ExtractString;

5) NULLIF Compares two expressions and returns NULL if they are equal.
Otherwise, the first expression is returned

The NULLIF function in SQL Server accepts two arguments and compares them to determine they
are the same or different. If both arguments are equal, it will return a NULL value. If the arguments
are not identical, they will return the first argument.

Example Compare two expressions:

SELECT NULLIF(25, 25);

Definition and Usage

The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first
expression.

Syntax

NULLIF(expr1, expr2)

Parameter Values

Parameter Description

expr1, expr2 Required. The two expressions to be compared

Example

Compare two expressions:

SELECT NULLIF('Hello', 'Hello');

Example

Compare two expressions:

SELECT NULLIF('Hello', 'world');

Example

Compare two expressions:

SELECT NULLIF('2017-08-25', '2017-08-25');

6) CURDATE Returns the current date

Example Return the current date:

SELECT CURDATE();

Definition and Usage

The CURDATE() function returns the current date.

Note: The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric).

Note: This function equals the CURRENT_DATE() function.

Syntax

CURDATE()

Example
Return the current date + 1:

SELECT CURDATE() + 1;

7) USER Returns the current MySQL user name and host name

8) VERSION Returns the current version of the MySQL database

String functions
are used to perform an operation on input string and return an output string.
Following are the string functions defined in SQL:
1. ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('t');
 Output: 116
2. CHAR_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL
Server. This function is used to find the length of a word.
Syntax: SELECT char_length('Hello!');
Output: 6

CHARACTER_LENGTH(): Doesn’t work for SQL Server. Use LEN() for

SQL Server. This function is used to find the length of a line.
Syntax: SELECT CHARACTER_LENGTH('geeks for geeks');
Output: 15

CONCAT(): This function is used to add two words or strings.

Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
Output: ‘GeeksforGeeks’

CONCAT_WS(): This function is used to add two words or strings

with a symbol as concatenating symbol.
Syntax: SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');
Output: geeks_for_geeks

FIND_IN_SET(): This function is used to find a symbol from a set of

symbols.
Syntax: SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');
Output: 2

FORMAT(): This function is used to display a number in the given

format.
Syntax: Format("0.981", "Percent");
Output: ‘98.10%’

INSERT(): This function is used to insert the data into a database.

Syntax: INSERT INTO database (geek_id, geek_name) VALUES
(5000, 'abc');
Output: successfully updated

INSTR(): This function is used to find the occurrence of an alphabet.

Syntax: INSTR('geeks for geeks', 'e');
Output: 2 (the first occurrence of ‘e’)
Syntax: INSTR('geeks for geeks', 'e', 1, 2 );
Output: 3 (the second occurrence of ‘e’)

LCASE(): This function is used to convert the given string into lower
case.
Syntax: LCASE ("GeeksFor Geeks To Learn");
Output: geeksforgeeks to learn

LEFT(): This function is used to SELECT a sub string from the left of
given size or characters.
Syntax: SELECT LEFT('geeksforgeeks.org', 5);
Output: geeks

LENGTH(): This function is used to find the length of a word.

Syntax: LENGTH('GeeksForGeeks');
Output: 13

LOCATE(): This function is used to find the nth position of the given
word in a string.
Syntax: SELECT LOCATE('for', 'geeksforgeeks', 1);
Output: 6

LOWER(): This function is used to convert the upper case string into
lower case.
Syntax: SELECT LOWER('GEEKSFORGEEKS.ORG');
Output: geeksforgeeks.org

LPAD(): This function is used to make the given string of the given
size by adding the given symbol.
Syntax: LPAD('geeks', 8, '0');
Output:
000geeks

LTRIM(): This function is used to cut the given sub string from the
original string.
Syntax: LTRIM('123123geeks', '123');
Output: geeks
 MID(): This function is to find a word from the given position and of
the given size.
Syntax: Mid ("geeksforgeeks", 6, 2);
Output: for

POSITION(): This function is used to find position of the first

occurrence of the given alphabet.
Syntax: SELECT POSITION('e' IN 'geeksforgeeks');
Output: 2

REPEAT(): This function is used to write the given string again and
again till the number of times mentioned.
Syntax: SELECT REPEAT('geeks', 2);
Output: geeksgeeks

REPLACE(): This function is used to cut the given string by removing

the given sub string.
Syntax: REPLACE('123geeks123', '123');
Output: geeks

REVERSE(): This function is used to reverse a string.

Syntax: SELECT REVERSE('geeksforgeeks.org');
Output: ‘gro.skeegrofskeeg’

RIGHT(): This function is used to SELECT a sub string from the right
end of the given size.
Syntax: SELECT RIGHT('geeksforgeeks.org', 4);
Output: ‘.org’

RPAD(): This function is used to make the given string as long as the
given size by adding the given symbol on the right.
Syntax: RPAD('geeks', 8, '0');
Output: ‘geeks000’

RTRIM(): This function is used to cut the given sub string from the
original string.
Syntax: RTRIM('geeksxyxzyyy', 'xyz');
Output: ‘geeks’

SPACE(): This function is used to write the given number of spaces.

Syntax: SELECT SPACE(7);
Output: ‘ ‘

STRCMP(): This function is used to compare 2 strings.

• If string1 and string2 are the same, the STRCMP function
will return 0.
• If string1 is smaller than string2, the STRCMP function will
return -1.
• If string1 is larger than string2, the STRCMP function will
return 1.
Syntax: SELECT STRCMP('google.com', 'geeksforgeeks.com');
Output: -1

SUBSTR(): This function is used to find a sub string from the a string
from the given position.
Syntax:SUBSTR('geeksforgeeks', 1, 5);
Output: ‘geeks’

SUBSTRING(): This function is used to find an alphabet from the

mentioned size and the given string.
Syntax: SELECT SUBSTRING('GeeksForGeeks.org', 9, 1);
Output: ‘G’

SUBSTRING_INDEX(): This function is used to find a sub string

before the given symbol.
Syntax: SELECT SUBSTRING_INDEX('www.geeksforgeeks.org', '.',
1);
Output: ‘www’

TRIM(): This function is used to cut the given symbol from the string.
Syntax: TRIM(LEADING '0' FROM '000123');
Output: 123

UCASE(): This function is used to make the string in upper case.

Syntax: UCASE ("GeeksForGeeks");
Output:
GEEKSFORGEEKS