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