Monday, December 26, 2011

String Reverse and Remove Non-Numeric Character for Index Operation

1 comments

I was working for a callcenter application where I had to write a query to find out the user name from phone numbers.
User would input any number from the end of the phone number and we had to match it with our database.
For example,
Table: phone_details
Username, phone_number
rumman,   651818
rohan,    65412 ext: 142
rock,     12 ext: 818
zen,      016-744-69818

And index on phone_number.

So if a user give 818 as search string, then the report should display the username -
rumman
rock
zen

Obviously, the query would be -
select * from phone_details where phone_number like '%818'.

But if we use the query in this way, we would not get any index search and the full table scan would definitly take long time.
So I planned to reverse both the user input and the database column value in the where clause and then created an index on the reverse string.

This is a Postgresql function to reverse the given string:

create or replace function string_reverse(prm_str text) returns text as
$$
DECLARE
reversed_string text;
BEGIN
reversed_string = '';
for i in reverse char_length(prm_str)..1
loop
reversed_string = reversed_string || substring(prm_str from i for 1);
end loop;
return reversed_string;
END
$$
language plpgsql;

This is a Postgresql function to remove nonnumeric character from the given input:

create or replace function remove_nonnumeric(text)
RETURNS text AS
$$
select regexp_replace($1,E'[^0-9]','','gs');
$$
LANGUAGE SQL;

Now, I used the query to fund out the user name where phone number ends with 818.

select * from phone_details where   string_reverse(remove_nonnumeric(phone_number))  like '818%';
Here 818 is user input.

For performance, I created index on string_reverse(remove_nonnumeric(phone_number)).

Continue reading →

Labels