SQL Basics - Using Functions (cont.)

Today we're going to talk about some more text manipulation functions that are a bit tricky to get right. The reason being that each application has different syntax for these functions. I will post all the different syntaxes for the popular programs and will indicate in a chart which syntax each program uses.

CONCATENATE

We already spoke about Concatenating, but I bring it up because the chart will tell us which syntax your program uses.

1. SELECT col1 col2 FROM tablename WHERE .....

2. SELECT col1 + col2 FROM tablename WHERE .....

3. SELECT CONCAT(col1,col2) FROM tablename WHERE .....

_________________________________________________

SUBSTRING

This function is similar to the LEFT and RIGHT function we learned about in last lesson. Except it takes a string from in middle of another string. For example, to take the third and fourth letter of the string in the column, we would enter it so:

1. SELECT SUBSTRING(colname FROM 3 FOR 2) FROM tablename WHERE .....

2. SELECT SUBSTR(colname,3,2) FROM tablename WHERE .....

3. SELECT MID(colname,3,2) FROM tablename WHERE .....

4. SELECT SUBSTRING(colname,3,2)FROM tablename WHERE .....

_________________________________________________

UPPER CASE

To retrieve the results all in upper case format, we use this command:

1. SELECT UPPER(colname) FROM tablename WHERE .....

2. SELECT UCase(colname) FROM tablename WHERE .....

_________________________________________________

LOWER CASE

To retrieve the results all in lower case format, we enter it like this:

1. SELECT LOWER(colname) FROM tablename WHERE .....

2. SELECT LCase(colname) FROM tablename WHERE .....

_________________________________________________

POSITION OF SUBSTRING IN STRING

To retrieve the position of a substring in a string, we use the SUBSTRING function. This is especially useful when finding out where a space is located to retrieve just the first word. To do that, we enter:

1. SELECT POSITION(' ' IN colname) FROM tablename WHERE.....

2. SELECT INSTR(colname, ' ') FROM tablename WHERE.....

3. SELECT CHARINDEX(' ',colname) FROM tablename WHERE.....

_________________________________________________

There are many more such functions, but these are the most important ones, especially if we're dealing with SQL injection. The following is a chart of the major Database applications and which syntax they use, using the numbered syntaxes above.

	Concatenate	Substring	Upper Case	Lower Case	Position
SQLite	1	1	1	1	1
DB2	1	2	1	1	1
Access	2	3	2	2	2
PostGres	1	1	1	1	1
Oracle	1	2	1	1	2
SQL Server	2	4	1	1	3
MySQL	3	1	1	1	1
Mimer	1	1	1	1	1
Sybase	1	4	1	1	3

We'll speak....