comparing RDBMS syntax

[Darren Dirt]

I found this brief reference that lists a dozen syntax quirks and "things to know" amongst the various more popular database systems...

http://search.cpan.org/~timb/dbd-summaries/

e.g. is it UCASE or UPPER when you need to use the LIKE command (which is usually case sensitive)...

http://search.cpan.org/~timb/dbd-summaries/dbd-oracle.pod#Case_Sensitivity_of_LIKE_Operator
http://search.cpan.org/~timb/dbd-summaries/dbd-mysql-msql.pod#Case_Sensitivity_of_LIKE_Operator
http://search.cpan.org/~timb/dbd-summaries/dbd-db2.pod#Case_Sensitivity_of_LIKE_Operator

now you know.

[Mr. Analog]

Here's something interesting from the world of DB2...

The UCASE or UPPER function is identical to the TRANSLATE function except that only the first argument (char-string-exp) is specified.

In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

So, in the world of DB2 it doesn't matter what you use, it's just calling the TRANSLATE SYSFUN

One thing I used to like playing with in MS SQL Server was the SOUNDEX value, I helped write a spell checker a while back. It was a lot of fun to find near matches, etc.

[Darren Dirt]

Yeah, I recall spending like 3 hours looking at sample code on the web that used SOUNDEX for various things, including a pretty basic but neato website search engine that had a "thesaurus" and did auto-correcting of your mistyped search terms.


BTW I found it interesting that though DB2/Oracle/MySQL claim to be ANSI SQL compliant (or whatever) they don't have standard syntax for things you'd expect to be obviously core requirements, like UPPER (or is it UCASE, or UPPERCASE, or TOUPPER, or TOUPPERCASE... hmmm... )

Thank goodness for web references for quirks like these -- as Einstein said...

[Mr. Analog]

Yeah, I recall spending like 3 hours looking at sample code on the web that used SOUNDEX for various things, including a pretty basic but neato website search engine that had a "thesaurus" and did auto-correcting of your mistyped search terms.


BTW I found it interesting that though DB2/Oracle/MySQL claim to be ANSI SQL compliant (or whatever) they don't have standard syntax for things you'd expect to be obviously core requirements, like UPPER (or is it UCASE, or UPPERCASE, or TOUPPER, or TOUPPERCASE... hmmm... )

Thank goodness for web references for quirks like these -- as Einstein said...

DB2 (8.1) extends most ANSI compliant syntax into existing system functions so, you can use either UCASE or UPPERCASE but it's just going to call another system function (good to know if you run into any threading issues on complex subqueries).

[Darren Dirt]

lol, I just found out that UCASE() or UPPER() are equivalent in DB2. I presume one is just an alias for the other.

Anyway, might be a good reference for comparing features for anyone who cares...

[Mr. Analog]

lol, I just found out that UCASE() or UPPER() are equivalent in DB2. I presume one is just an alias for the other.

Anyway, might be a good reference for comparing features for anyone who cares...

Yes, it calls the TRANSLATE system function.