Rick Caminiti dotCOM

This is a rather strange topic for a Business Intelligence blog, and I’ll probably forever kick myself for making it my first post, but nonetheless, I ran into an issue today trying to get around some ridiculous BO messes. One of which was that my hacks had to all be in Javascript. The other was that I had to go back and forth between POSTing and GETing http variables.

Without getting into any more ugly details, the bottom line is that I always knew there was a nifty little PHP function called urlencode(), but I didn’t know exactly what was available in Javasctipt. Thanks to a quick look on phpbuilder, I realized that there was a built-in function simply called escape() that would work just fine. I needed this because I was building a query string (&abc=1&xyz=2&string=blah) but since I was flipping back and forth between GET and POST methods, I needed to encode it.

The escape() function actually worked pefectly for me, but as a side-note, as cass-hacks explains, there is a distinc difference between urlencode() and escape(), and this would be a way to mimic it more accurately:


function urlencode(str) {
return escape(str).replace('+', '%2B').replace('%20', '+').replace('*', '%2A').replace('/', '%2F').replace('@', '%40');
}


function urldecode(str) {
return unescape(str.replace('+', ' '));
}


While delving into the wonderful (*ahem) world of SQL server, I was attempting to parse through some text fields and needed to find an alternative to the Oracle instr() function.

The syntax for the INSTR Oracle function is:


instr( string1, string2 [, start_position [, nth_appearance ] ] )


string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

The syntax for CHARINDEX in T-SQL is


CHARINDEX ( expression1 ,expression2 [ , start_location ] )


expression1 Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.

expression2 Is a character expression to be searched.

start_location Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.

First of all, when I’m trying to find metada in Oracle, I am usually using Toad, and have no problem finding what I need because it’s such a great too. However, even in SQL-Plus, it’s still really easy to find what I need by querying the data dictionary:


select *
from all_columns
where owner = user
and table_name = 'tablename'

… Or even easier, if it’s in the schema I’m already in:

select *
from user_columns
where table_name = 'tablename'


It seems that it’s still pretty easy in SQL Server to do this, but off the bat, it’s not exactly as intuitive. Apparently you can go through sysobjects, but even Microsoft suggests against it, considering they reserve the right to change any and all system tables from version to version.

There are alternatives though; views in INFORMATION_SCHEMA:
For instance, to view tables:

select *
from INFORMATION_SCHEMA.TABLES

… And for columns:

select *
from INFORMATION_SCHEMA.Columns
where table_schema = 'schemaname'
and table_name = 'tablename'


Here’s some more great information about INFORMATION SCHEMA: Here