42  SQL string functions

42.1 SQL Standard string functions

• coalesce(x,y,…)

SQL standard. returns a copy of its first non-null argument. Useful, for replacing null values.

• oracle documentation for coalesce

• SQL Server documentation for coalesce

• sqlite documentation for coalesce

• concat

Normally, string concatenation is accomplished using || operator but this function could also be used. SQL Server uses + for concatenation.

SQL server has also CONCAT_WS function.

• oracle documentation for function

• SQL Server documentation for function

• sqlite documentation for function

• length(X)

return number of characters (not bytes) of given string.

For SQL server, LEN function is used.

• oracle documentation for length
• SQL Server documentation for length
• sqlite documentation for length

sqlite

SELECT LENGTH('atilla')

Oracle

SELECT LENGTH('atilla') from dual;

SQL Server

SELECT LEN('atilla')

SELECT LENGTH(FirstName) from Customer c;

• lower(X)

return lowercase of given string X

• oracle documentation for lower
• SQL Server documentation for lower
• sqlite documentation for lower

instr(X,Y)

Find the first occurrence of Y in the given string X and return position number of it. if it could not find, return 0.

• replace(X,Y,Z)

The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X.

• oracle documentation for function
• SQL Server documentation for function
• sqlite documentation for function

soundex(X)

From SQL Server documentation

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

• oracle documentation for soundex

• SQL Server documentation for soundex

• [sqlite documentation for soundex](https://www.sqlite.org/lang_corefunc.html#soundex

• substr(X,Y,Z)

• substr(X,Y)

• substring(X,Y,Z) (alias for substr)

• substring(X,Y) (alias for substr)

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1.

SQL Server has substring only.

• oracle documentation for function
• SQL Server documentation for function
• sqlite documentation for function

trim(X) trim(X,Y)

The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X. 

• rtrim(X)
• rtrim(X,Y)

right trim character values given in Y from the string X. If Y is not given, removes spaces

• oracle documentation for rtrim

• SQL Server documentation for rtrim

• sqlite documentation for rtrim

• ltrim(X)

• ltrim(X,Y)

left trim character values given in Y from the string X. If Y is not given, removes spaces

• oracle documentation for LTRIM
• SQL Server documentation for LTRIM
• sqlite documentation for LTRIM

SELECT
       '       Hello World        '  as A
, TRIM('       Hello World        ') as TA
,LTRIM('       Hello World        ') as LTA
,RTRIM('       Hello World        ') as RTA

SELECT
       '@@@@@@@Hello World@@@@@@@@'  as B
, TRIM('@@@@@@@Hello World@@@@@@@@') as TB
,LTRIM('@@@@@@@Hello World@@@@@@@@') as LTB
,RTRIM('@@@@@@@Hello World@@@@@@@@') as RTB

We need to use second argument for trim char.

SELECT
       '@@@@@@@Hello World@@@@@@@@'  as C
--, TRIM('@@@@@@@Hello World@@@@@@@@','@') as TC
,LTRIM('@@@@@@@Hello World@@@@@@@@','@') as LTC
,RTRIM('@@@@@@@Hello World@@@@@@@@','@') as RTC

SQLite normal trim does not accept second trim character but LTRIM and RTRIM accepts it.

• oracle documentation for function
• SQL Server documentation for function
• sqlite documentation for function

upper(X) Uppercase of given string X. sqlite works with ascii only.

select UPPER('atilla özgür');

ATILLA öZGüR

• oracle documentation for function
• SQL Server documentation for function
• sqlite documentation for function

42.2 SQL string functions: sqlite

• sqlite documentation string functions

format printf(FORMAT,…) (alias of format)

SQL function works like the C-language function and the printf() function from the standard C library.

glob(X,Y)

whether a string,Y, matches a pattern X. Equal to the expression “Y GLOB X”.

select glob('ati','atilla'); 
select glob('*ati*','atilla');

octet_length(X) The octet_length(X) function returns the number of bytes in the encoding of text string X.

quote(X)

The quote(X) function returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed.

Useful for sql injection.

unicode(X)

The unicode(X) function returns the numeric unicode code point corresponding to the first character of the string X. I

42.3 SQL string functions: SQL Server

• SQL Server documentation string functions

FORMAT

format(value,format_string)

format_string should be .net format string

• SQL Server documentation for format

42.4 SQL string functions: Oracle

• Oracle documentation Function