Avoid 'SQL LIKE' - Use Full Text Search

Hello random googler,

Welcome back

Do u often find your self needing to do 'dirty' SQL LIKEs like the following;

SELECT member_id 

FROM vw_members

WHERE upper(member_name) LIKE 'JAY%' OR

upper(member_county) LIKE 'NAI%' OR

upper(staff_no) LIKE 'S01%' OR 

upper(mobile_no) LIKE '072%' OR 'i am tired of this stuff and this is only one permutation assuming the first 3 characters hav been filled...ouuuch'

? (yes question mark... this is where the question ends!!!)


If you answer is yes OR 'Y%'  ;-) ;-) then you are ready for FULL TEXT SEARCH

ONE TECHNIQUE...
Make your SQL VIEWS read for FTS by always having a column called 'ts_doc' that should contain the 'searchable' columns(of ur choice) ??????????


EG (postgres)

CREATE OR REPLACE VIEW vw_members AS

SELECT members.member_id, members.member_name, member.staff_no, members.mobile_no, andanyothercolumnyouwhishcoziassumeuknowsql,

 to_tsvector(COALESCE(members.member_name,'')||' '||COALESCE(member.staff_no,'') ||' '|| COALESCE(members.mobile_no,'')) as ts_doc

So that in your queries just become

SELECT blahblah

FROM vw_members

WHERE ts_doc @@ to_tsquery(' searchQuery:*') ";

NB: searchQuery is nothing but a cleanup to create a valid FTS query:-
searchQuery = searchQuery.replace(" ",":* & ") - in java, but of course u can use ur prefered technique... php, sql,

CONCLUSION
With the above u can use more than one search text(in whatever order or case) to narrow down the result..
for example with searchQuery like 'mik DS1 nai' it will search in all the columns (defined in ts_doc) for words starting with mik or DS1 or nai... thereby matching all entries for nairobi, all names starting with mik eg mike,mikel etc etc

NB: approach is 'begins with....'
(those who do LIKE '%xyz%' look for another evangelist !!!!)

example is in postgres

adios

Oracle and Postgres Cummulative Sum aka Running Totals

Hello Random Googler !!!

You landed here today... u r welcome.

Now consider the following output.

The 'interesting' column is the last one

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	3000
Shamim	01-JUL-13	1000	4000
Abdul	01-JUL-13	1000	5000
Jim	01-AUG-13	1000	6000
ZeGuru	05-SEP-13	1000	7000
Duli	06-OCT-13	1000	8000

Another example
(grouped by date... sorry partitioned by date)

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	1000
Shamim	01-JUL-13	1000	2000
Abdul	01-JUL-13	1000	3000
Jim	01-AUG-13	1000	1000
ZeGuru	05-SEP-13	1000	1000
Duli	06-OCT-13	1000	1000

The first one was achieved by;

SELECT name, date, amount, sum(amount) over(order by payment_id) cummulative_sum
FROM vw_payment

THE second was achieved by;

SELECT name, date, amount, sum(amount) over(partition by date, order by payment_id) cummulative_sum
FROM vw_payment

(the partition by clause was used to restart the running totals for each date)


do i need to say that payment_id is just the PK (incremental/serial integer) of the original table ?

By the way this works for both PostgreSQL and Oracle

Again u r welcome