Recent Pos

IPv4 Address formatting and sorting in DB


database text format sort string algorithm sql oracleMany time while doing some processing like sorting and searching on a list of IP addresses we find it difficulty to do. The problem is when we try to store IP address inside a DB it should go as a String. And DB treat it as a simple string only, it does not have intelligence to identify it as IP address. So Sorting a string in DB is a very frustrating work.
To simplify the problem we can create one DB function and use it to format the IP address as we want and then we can easily sort it and search it easily. Following is a code snippet that we can use to construct our function:

After compiling the function, we can use it anywhere in SQL. The following is an example we can use it inside a simple SQL statement to use it:

The output will be So now the IP address each octet are having same length of character which is now easy to sort using SQL ORDER BY clause.
Next->Overcome Oracle SQL IN() clause limitation

No Comments

Post a Comment