Search in SQL for a value containing a ‘ character


So, you are stuck with a problem…

You need to find User names that are stored in the DB Users table, and that contain the apostrophe character. (eg. Susan D’Souza)

How do you go about it?

You know the LIKE keyword has to be used somewhere. But, when you add the apostrophe character in the LIKE criteria, the query gives an error (as displayed below) on execution.
SQL LIKE query with apostrophe

Error for SQL LIKE query with apostrophe

It took a while for my colleague and I to find the resolution to this, but thanks to the world wide web, we did find it. 😀

And now I am writing it down here for the benefit of any one else who stumbles across this entry while searching online for the solution. (and of course, for my future reference)

And it’s a very simple solution –
Instead of just one apostrophe character, write 2 of them consecutively in the LIKE criteria.

An Example is given below-

>> SELECT * FROM Users where UserName like ‘%D”Souza%’

The above query will give you the list of all the users that have “D’Souza” in their name.

Happy Querying and see you soon… 🙂

Advertisements

Ordering of Strings according to numbers contained in them


This is an error that is more common than you would think.

Take an instance – There is a column in the Database table such as a Profile ID, and the developer thinks of sorting the records according to the Profile ID in either an ascending, or a descending order. eg. Sorting records by descending Profile ID to show the latest added records first.

What happens is that the Profile Id is alphanumeric, and default sorting of such records is not exactly as one would expect.

Below is the description of a recent bug I raised. You will understand what I’ve been trying to say by reading it.

Bug Description – “The ‘Top Deals in your City’ section of Home page is required to show the latest entered bike listings.
However, Latest Listings were not being displayed as expected. Issue is as  explained below:

Listings were being Ordered by ProfileID, and not by EntryDate.
Hence, a listing with ProfileID ‘S27’ that was entered on ‘2012-08-27 20:24:26.067’ was considered newer than a listing with ProfileID ‘S237’ that was entered on ‘2013-02-05 11:13:27.637’

Strings are ordered as ascending/descending in a way different from the ordering of integers.
Hence, while ordering, ‘S27’ was considered to be greater than ‘S237’ ”

Have I succeeded in explaining this to you yet? Let me try again..

You have the following values:

A1

A2

A10

A108

A20

A3

You want to order them in the ascending order. What would you expect the result to be? I’m sure everyone would expect it to be as below;

A1

A2

A3

A10

A20

A108

But, the default sort functions in almost every programming language would return the below result. The records are sorted in ASCII order, and not in Alphabetical order.

A1

A10

A108

A2

A20

A3

What can we do to resolve this problem? Let’s see…

1. The first and most simple solution would be to not use the alpha-numeric column for sorting the records.

Instead, one could use a Date column to sort records (as mentioned in my bug description above), or one could use an ID column that contains only numbers and is meant for sorting the records.

Proper planning of Database architecture could help prevent this problem from arising.

2. If the only way to sort records is to use the alpha-numeric column, then one can use “Natural Sorting”. Following are some good links that would help you understand this subject more.

The Alphanum Algorithm

Sorting for Humans : Natural Sort Order

Natural Sorting in C#

Compact Python Human Sort

Find links to your website through Google


Thanks to a post in another blog (http://blog.nerdstogeeks.com/2009/06/track-links-to-your-site-from-google.html), I learnt a new thing yesterday.

You can find out all the references to your website in the world wide web by this simple trick.

Just go to Google search, type in “link: ‘Your website URL’“, and press Enter.

Google will list out all the links to your website URL from where people can visit your page. Nice, huh?