Tutorial 7 - Adding an Apostrophe into a column using SQL

Question: How do I add an apostrophe into a column using SQL?

Solution: Although many people abuse the apostrophe there's a lot of times when it needs to be used.

Example:

INSERT INTO SLOGANS

(FULL_NAME, SLOGAN)

VALUES

('Andrew Krone', 'It''s time Andy''s website stopped boring people');

Hope everyone can see the double quotes, pretty simple stuff.
-Andy

Tutorial 6 - The Oracle "CASE" Statement

The "case" statement can be used much like an if-then-else statement but with less syntax.



SELECT BIKE_NAMES,

CASE
WHEN 'TREK' THEN 'Lance''s Trek'
WHEN 'LITESPEED' THEN 'Lance''s other Trek'
ELSE 'Obsolete Bike'

END
FROM BIKES;


Transalation: Basically the statement above says select the column "BIKE_NAMES" from the table "BIKES". When the column shows "TREK" replace with "Lance's Trek" (notice the intentional double apostrophe to display "Lance's"). The statement continues to say when the column "BIKE_NAMES" contains "LITESPEED" replace the row with "Lance's other Trek" (notice the intentional double apostrophe to display "Lance's"). Finally, the statement has an else that displays "Obsolete Bike" should there be ANY other values in the "BIKE_NAMES" column. I think "END" speaks for itself with the table "BIKES" being selected AFTER the "CASE" statement.

-Andy

Notes: Programmatically speaking the "CASE" statement is cleaner and more forward then using if-then-else, especially if you might be creating a basic view.

Applies to:Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Tutorial 5 - The Oracle "REPLACE" Function

Here's the definition of "REPLACE" according to the Oracle website located at "http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm".






An example of using the replace function from a column (say car_names) from a table (say cars) might look like this:


SELECT REPLACE('FORD','F','CRAPPY F') CAR_NAMES FROM CARS;

Translated, this statement says, replace the letter "F" from FORD with "CRAPPY F" to make the new string "CRAPPY FORD" wherever "FORD" exists in the column "CAR_NAMES" from the table "CARS".

The replace function is also useful for removing commas or decimal points that might not be needed from the string.

-Andy

Tutorial 4 - Upgrading Broken iTunes

Problem: iTunes breaks when upgrading or installing due to a previous version not uninstalling correctly.

Solution: Download the Windows Installer Cleanup Utility located at http://support.microsoft.com/kb/290301.

You'll have to install the tool and remove the old iTunes AND Quicktime installs with the tool, THEN install the latest iTunes.

Not everyone may believe or even accept this radical idea, but iTunes on the PC is very buggy and a really clumsy app, something totally unacceptable for a video/photo/audio program.

Anyway, I've resisted upgrading iTunes for fear I will be forced to use iTunes for virtually every listening or viewing task AND

Ironically, it's Microsoft who provides the tool to make an iTunes upgrade possible.

Stupid Apple

-Andy

Tutorial 2 - The Oracle "SUBSTR" Function

Here's the definition of "SUBSTR" according to the Oracle website located at "http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions162.htm".




"The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points."

For most purposes "SUBSTR" is all you'll need, the SUBSTRB, SUBSTR2 and SUBSTR4 are not very common.

Summary: SUBSTR ('length', 'startpositioninstring' )

Here's some syntax if you're just running a simple select:
SELECT SUBSTR('Andrew',3,4) "SOMECOLUMNNAME" FROM SOMETABLE;

Output: drew

Here's some syntax if you're just running a simple select:
SELECT SUBSTR('Andrew',-5,4) "SOMECOLUMNNAME" FROM SOMETABLE;

Output: ndre



Note:
-If position is 0, then it is treated as 1.
-If position is positive, then Oracle Database counts from the beginning of char to find the first character.
-If position is negative, then Oracle counts backward from the end of char.
-If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.



Applies to :
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Tutorial 1 - The Oracle "Trim" Function

The trim function removes all specified characters either from the beginning or the ending of a string. This is very useful when you want to cleanup data that may have different column lengths and unusable or annoying characters or blank spaces.


An example would be a city. Some cities may be long like, "San Francisco" whereas others might be short. Because the column may include leading or trailing blank spaces it's a good idea to "trim" the field.


This function can also be useful to cleanup data for validation, some people may unintentionally add a blank space in a form field (i.e. " Andrew Krone" instead of just "Andrew Krone".


Ultimately, it just makes data look cleaner.


For example:

trim(' Ventura ') would return 'Ventura'
trim(' ' from ' tech ') would return 'tech'
trim(leading '0' from '000123') would return '123'
trim(trailing '1' from 'Tech1') would return 'Tech'
trim(both '1' from '123Tech111') would return '23Tech'

Applies to :

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

(thanks go to techonthenet.com)