SQL String Functions

SQL String Functions
Nikhil
Published on 2021-08-15 02:42:01

String functions perform operations on string values and return either numeric or string values.

Using string functions, you can, for example, combine data, extract a substring, compare strings, or convert a string to all uppercase or lowercase characters.

SQL Concatenate

In (standard ANSI/ISO) SQL, the operator for string concatenation is ||. This syntax is supported by all major databases except SQL Server:

SELECT 'Hello' || 'World' || '!'; --returns HelloWorld!

Many databases support a CONCAT function to join strings:

SELECT CONCAT('Hello', 'World'); --returns 'HelloWorld'

Some databases support using CONCAT to join more than two strings (Oracle does not):

SELECT CONCAT('Hello', 'World', '!'); --returns 'HelloWorld!'

In some databases, non-string types must be cast or converted:

SELECT CONCAT('Foo', CAST(42 AS VARCHAR(5)), 'Bar'); --returns 'Foo42Bar'

Some databases (e.g., Oracle) perform implicit lossless conversions. For example, a CONCAT on a CLOB and NCLOB yields a NCLOB. A CONCAT on a number and a varchar2 results in a varchar2, etc.:

SELECT CONCAT(CONCAT('Foo', 42), 'Bar') FROM dual; --returns Foo42Bar

Some databases can use the non-standard + operator (but in most, + works only for numbers):

SELECT 'Foo' + CAST(42 AS VARCHAR(5)) + 'Bar';

On SQL Server < 2012, where CONCAT is not supported, + is the only way to join strings.

SQL Length

SQL Server

The LEN doesn't count the trailing space.

SELECT LEN('Hello') -- returns 5
SELECT LEN('Hello '); -- returns 5

The DATALENGTH counts the trailing space.

SELECT DATALENGTH('Hello') -- returns 5
SELECT DATALENGTH('Hello '); -- returns 6

It should be noted though, that DATALENGTH returns the length of the underlying byte representation of the string, which depends, i.a., on the charset used to store the string.

DECLARE @str varchar(100) = 'Hello ' --varchar is usually an ASCII string, occupying 1 byte per char
SELECT DATALENGTH(@str) -- returns 6
DECLARE @nstr nvarchar(100) = 'Hello ' --nvarchar is a unicode string, occupying 2 bytes per char
SELECT DATALENGTH(@nstr) -- returns 12

Oracle

Syntax: Length ( char )

Examples:

SELECT Length('Bible') FROM dual; --Returns 5
SELECT Length('righteousness') FROM dual; --Returns 13
SELECT Length(NULL) FROM dual; --Returns NULL

See Also: LengthB, LengthC, Length2, Length4

Trim empty spaces

Trim is used to remove write-space at the beginning or end of selection

In MSSQL there is no single TRIM()

SELECT LTRIM(' Hello ') --returns 'Hello '
SELECT RTRIM(' Hello ') --returns ' Hello'
SELECT LTRIM(RTRIM(' Hello ')) --returns 'Hello'

MySql and Oracle

SELECT TRIM(' Hello ') --returns 'Hello'

Upper & lower case

SELECT UPPER('HelloWorld') --returns 'HELLOWORLD'
SELECT LOWER('HelloWorld') --returns 'helloworld'

Split

Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function.

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Result:

value
-----
Lorem
ipsum
dolor
sit
amet.

Replace

Syntax:

REPLACE( String to search, String to search for and replace, String to place into the original string )

Example:

SELECT REPLACE( 'Peter Steve Tom', 'Steve', 'Billy' ) --Return Values: Peter Billy Tom

REGEXP

MySQL Version ≥ 3.19

Checks if a string matches a regular expression (defined by another string).

SELECT 'bedded' REGEXP '[a-f]' -- returns True
SELECT 'beam' REGEXP '[a-f]' -- returns False

Substring

Syntax is: SUBSTRING ( string_expression, start, length ). Note that SQL strings are 1-indexed.

SELECT SUBSTRING('Hello', 1, 2) --returns 'He'
SELECT SUBSTRING('Hello', 3, 3) --returns 'llo'

This is often used in conjunction with the LEN() function to get the last n characters of a string of unknown length.

DECLARE @str1 VARCHAR(10) = 'Hello', @str2 VARCHAR(10) = 'FooBarBaz';
SELECT SUBSTRING(@str1, LEN(@str1) - 2, 3) --returns 'llo'
SELECT SUBSTRING(@str2, LEN(@str2) - 2, 3) --returns 'Baz'

Stuff

Stuff a string into another, replacing 0 or more characters at a certain position.

Note: start position is 1-indexed (you start indexing at 1, not 0).

Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

Example:

SELECT STUFF('FooBarBaz', 4, 3, 'Hello') --returns 'FooHelloBaz'

LEFT - RIGHT

Syntax is:

LEFT ( string-expression , integer )
RIGHT ( string-expression , integer )
SELECT LEFT('Hello',2) --return He
SELECT RIGHT('Hello',2) --return lo

Oracle SQL doesn't have LEFT and RIGHT functions. They can be emulated with SUBSTR and LENGTH.

SUBSTR ( string-expression, 1, integer )
SUBSTR ( string-expression, length(string-expression)-integer+1, integer)
SELECT SUBSTR('Hello',1,2) --return He
SELECT SUBSTR('Hello',LENGTH('Hello')-2+1,2) --return lo

REVERSE

Syntax is: REVERSE ( string-expression )

SELECT REVERSE('Hello') --returns olleH

REPLICATE

The REPLICATE function concatenates a string with itself a specified number of times.

Syntax is: REPLICATE ( string-expression , integer )

SELECT REPLICATE ('Hello',4) --returns 'HelloHelloHelloHello'

Replace function in sql Select and Update query

The Replace function in SQL is used to update the content of a string. The function call is REPLACE( ) for MySQL, Oracle, and SQL Server.

The syntax of the Replace function is:

REPLACE (str, find, repl)

The following example replaces occurrences of South with Southern in the Employees table:

FirstName Address
James South New York
John South Boston
Michael South San Diego

Select Statement :

If we apply the following Replace function:

SELECT
FirstName,
REPLACE (Address, 'South', 'Southern') Address
FROM Employees
ORDER BY FirstName

Result:

FirstName Address
James Southern New York
John Southern Boston
Michael Southern San Diego

Update Statement :

We can use a replace function to make permanent changes in our table through following approach.

Update Employees

Set city = (Address, 'South', 'Southern');

A more common approach is to use this in conjunction with a WHERE clause like this:

Update Employees
Set Address = (Address, 'South', 'Southern')
Where Address LIKE 'South%';

INSTR

Return the index of the first occurrence of a substring (zero if not found)

Syntax: INSTR ( string, substring )

SELECT INSTR('FooBarBar', 'Bar') -- return 4
SELECT INSTR('FooBarBar', 'Xar') -- return 0

PARSENAME

DATABASE : SQL Server

The PARSENAME function returns the specific part of a given string(object name). object name may contain string-like object name, owner name, database name, and server name.

More details MSDN:PARSENAME

Syntax

PARSENAME('NameOfStringToParse',PartIndex)

Example

To get object name use part index 1

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',1) // returns `ObjectName`
SELECT PARSENAME('[1012-1111].SchoolDatabase.school.Student',1) // returns `Student`

To get schema name use part index 2

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',2) // returns `SchemaName`
SELECT PARSENAME('[1012-1111].SchoolDatabase.school.Student',2) // returns `school`

To get the database name use part index 3

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',3) // returns `DatabaseName`
SELECT PARSENAME('[1012-1111].SchoolDatabase.school.Student',3) // returns `SchoolDatabase`

To get the server name use part index 4

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',4) // returns `ServerName`
SELECT PARSENAME('[1012-1111].SchoolDatabase.school.Student',4) // returns `[1012-1111]`

PARSENAME will return null is the specified part is not present in the given object name string

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website