XPATH for case insensitive search (translate function)

Guyz!

You know XML is case sensitive. But there are different situations when we have to deal with case insensitive search. Since XPATH is also for XML it has to be case sensitive. But can I search something in an XML document. I want my search to be case insensitive. This is because I know that my search text or pattern may be in a combination of different cases. But is there a workaround possible so that I could search an XML document for a pattern using case insensitive search.


You would be glad to know there is a way around. This is achieved using translate function in XPATH. This function is actually used to replace any combination of characters with some other combination of characters. The character in the pattern is replaced by character on the same position in the replace expression.

The syntax is as follows:

translate( text, pattern, replace expression)

Lets do an exercise. For example we have an XML element ‘MyDate’ which has information about data in the format: MM-DD-YYYY. We want to change hyphens (-) in the date to forward slashes (/). The translate function can be written like this:

translate(MyDate, ‘-’, ‘/’)

Now come to our case, what if we convert the case of our text to lower. Now we change the case of our pattern to lower. Now if we would do a search, we can do it easily. Lets write our translate function for this:

translate( MyText, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’, ‘abcdefghijklmnopqrstuvwxyz’)

Now the first character in the pattern is ‘A’. If this is found in MyText, it would be replaced with ‘a’. The second character is ‘B”, when found, it would be replaced with second character in MyText. This would be same for every character in the pattern string.

Now you must be thinking if the length of pattern is greater than replace expression. In that case all those positions in the pattern expression which have no character in the corresponding positions in the replace expression, are just removed from MyText. So if we want to remove all hypens from our date, we would use translate function like this:

Translate(MyDate, ‘-’, ‘’)

And our XPATH expression might be like this:

//MyXMLElement[translate(@MyDate, ‘-’, ‘’) = ‘12022009’]

If you want to test your XPATH expressions, then there is an excellent online free tool. This link is as follows:

XPATH Online tool

Object Search (SQL Server 2008)

It has always been difficult to find out the exact object if we don't exactly know the name or type of an object. In SQL Server Management Studio 2005, it is impossible to search any object because no such option exists in the first place. As a database professional, I don't remember the names of all the objects all the times. I may remember what the name should be like but not the exact name. If I know the exact name, I don't remember whether it is stored procedure or function (if there is no naming convention). To resolve all these problems, I need a search tool within the management studio environment.

SQL Server 2008 seems to answer our prayers by providing 'Object Search'. The feature is available as part of 'Object Explorer Details'. This window can be used independently and also with 'Object Explorer' window. This provides primitive search of database objects in SQL Server 2008. By 'primitive', I mean it has several limitation which would be felt by professionals using this tool.

To search any object, just type in the search bar in 'Object Explorer Details' window. There is wild card character available. This is same i.e. '%', which means zero or more characters. This character has the same behavior as T-SQL.



Object Explorer Details window can run in isolation or with the Object Explorer window.

Limitations:
1. It is not possible to search a particular type of object e.g. you can not search all stored procedures starting from 'CDC_'.

2. It is not possible to search negation e.g. we can not search all the objects which does not contain 'CDC_'.

3. Software professionals are generally comfortable with regular expressions for search. Regular Expressions are not supported for search.

Google Custom Search (SQL Server)

Whenever you need any information you start Google, type the term and hit enter. The query may be about any dictionary request, engineering problem and everything else in the world.

Now this information on tip is very good as you can search the world very easily. But to find out the relevant information among the result is very difficult. We need a way so that we could search only the specific sites specializing in the specific field. Google introduced a Custom Search option few years back. Using the same provision, I have created a Custom Search for Microsoft SQL Server.

Google Custom Search

Please visit the above search engine to query your specific terms.