SQL – Various Functions

Character Index

SQL Server

SELECT CHARINDEX('WORD', 'FIND WORD IN THIS SENTENCE.')
Returns 6 (WORD starts in the 6th position of the sentence.)

Oracle

SELECT INSTR('FIND WORD IN THIS SENTENCE', 'WORD') FROM DUAL;
Returns 6 (WORD starts in the 6th position of the sentence.)

SUBSTRING FUNCTION

SQL Server

Syntax - SUBSTRING(string, starting position, ending position)
SELECT SUBSTRING('WORD',1,2)
Returns 'WO'

Oracle

Syntax - SUBSTRING(string, starting position, ending position)
SELECT SUBSTR('WORD',1,2)
Returns 'WO'

Reverse Function

If for some reason, you need to reverse the direction of characters or
numbers in a field, you can use the reverse function.

SQL Server

SELECT REVERSE('WORD')
Returns 'DROW'

Oracle

SELECT REVERSE('WORD') FROM DUAL;
Returns 'DROW'

Length Function

The length function returns an integer value representing the length of the
selected field or given value.

SQL Server

SELECT LEN('WORD')
Returns 4

Oracle

SELECT LENGTH('WORD') FROM DUAL;
Returns 4

Concatenate

Note, when joining two or more fields together, both fields must be a
string value. If one of the fields is a numeric value, the numeric field
must be converted to a string.

SQL Server

SELECT Emp_LastName + ', ' + Emp_FirstName FROM Employee;
Returns 'LastName, FirstName'

Oracle

Two pipe symbols (||) are used to concatenate values in Oracle

SELECT Emp_LastName || ', ' || Emp_FirstName FROM Employee;

The CONCAT Function can only join two strings together. If you need to
join more than two strings, you will need to use the method above.

SELECT CONCAT(Emp_LastName, Emp_FirstName) FROM Employee;
Returns 'LastName, FirstName'

Character Function

Character Function – Returns the Character from a given character code

SQL Server

SELECT CHAR(65) + CHAR(66) + CHAR(67)
Returns ABC

Oracle

SELECT CHR(65) || CHR(66) || CHR(67) FROM dual;
Returns ABC

ASCII Character Table

Replace Statement

The Replace Statement replaces all instances of a specified string value
with a new value.

Syntax

REPLACE(Field Name, Search String, New String)

SQL Server

SELECT REPLACE(FIELD1,'/','-') FROM TABLE

Oracle

SELECT REPLACE(FIELD1,'/','-') FROM TABLE;

Select Top N From

The Replace Statement replaces all instances of a specified string value
with a new value.

Syntax

REPLACE(Field Name, Search String, New String)

SQL Server

SELECT REPLACE(FIELD1,'/','-') FROM TABLE

Oracle

SELECT REPLACE(FIELD1,'/','-') FROM TABLE;

#SQLServer #Oracle #Reference