Greg Owen
Over 27 Years of Programming Experience
Programming Examples, Portfolio, and More
Home

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
Some words from the sponsors...

Add a C# Dropdown to a Bootstrap Modal Dialog

This example combines C# and Bootstrap to create a bootstrap modal dialog containing an asp dropdown and an asp button to retrieve...

Continue Reading

Use C# to Populate Dropdown Lists With XML

The code below opens an xml file, reads the data from the xml file, dynamically adds a label for the dropdown list, and adds the dropdown list to the asp.net page...

Continue Reading

Add Style Properties to ASP.Net Controls to Improve Appearance

The default formatting for ASP.Net controls is very dated.  The default formatting is basically the same as the formatting in Visual Basic 1.0 ...

Continue Reading

Export To A Text File From A DataGridView Control

Add a button control to Form1.
Add a DataGridView Control to Form1....

Continue Reading