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

Advertisements

2 Responses to “SQL Function to get desired characters”


  1. 1 Ron December 17, 2009 at 1:43 am

    This is a neat little function. However, using a user defined function in the predicate will cause the query optimizer to use scan instead of a seek, causing performance issues.

    IMHO, you are better off sanitizing your data.

    Check out the execution plan on the queries below (using the AdventureWorks database).

    SELECT
    *
    FROM
    Person.Person
    WHERE
    dbo.GetCharacters(LastName,’A-Za-z’) = ‘Smith’ ;

    SELECT
    *
    FROM
    Person.Person
    WHERE
    LastName = ‘Smith’ ;

    • 2 Raja December 17, 2009 at 1:50 pm

      Thanks for the info Ron :-)…..I knew it is gonna be a problem when I have an UDF in my where clause. The problem is I am dealing with two different datasets, one the application database and the other from mainframe. I could not sanitize either of them since one is given to us by Customer and the other is being used for reports. I am in the middle of two worlds which is kind of stringent. Anyways that was good information.

      Thanks,
      Raja


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: