Archive for December, 2009

SQL Function to get desired characters

We had a requirement to check the data in a table by stripping off special characters since the match in the filter was getting too much (ex. Robin Jr. wont match Robin Jr but we wanted to match it). So searched for a generalized function which would do the job and found this:

CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN

 While @myString like '%[^' + @validChars + ']%'
 Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

 Return @myString
END
Go

So while writing the where clause I used this function and it works like a charm.


Select ColumnA, ColumnB From TableA WHERE dbo.GetCharacters(ColumnB,'a-z') = dbo.GetCharacters(@SearchItem,'a-z')

You can use the above function to get just characters (a-z) or just numbers (0-9) or alpha numeric (0-9a-z) or specific characters like (abcd). This function is pretty effective since it uses regular expression to do the job.

Hope this helps.

Happy Programming!!!

Cheers,
Raja

Javascript – Getting URL Values

I found this nice function at Snipplr. This is a simple function but the thought behind such a simple function amazed me. I used to get the query string values using <% %> and adding the values to a hidden span. This is much more effective since it gets the values in name value pairs.


// Read a page's GET URL variables and return them as an associative array.
function getUrlVars()
{
 var vars = [], hash;
 var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');

 for(var i = 0; i < hashes.length; i++)
 {
 hash = hashes[i].split('=');
 vars.push(hash[0]);
 vars[hash[0]] = hash[1];
 }

 return vars;
}

Hope this helps.

Happy Programming!!!

Cheers,

Raja