Thursday, January 31, 2008

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.

ConcatenateSubstringUpper CaseLower CasePosition
SQLite11111
DB212111
Access23222
PostGres11111
Oracle12112
SQL Server24113
MySQL31111
Mimer11111
Sybase14113

We'll speak....

No comments:

Contact Form