>Hi,
>
>I have a generic "search" class that I am improving. Before a user can do an add to a table, it searches about 12 different ways and then presents the results to the user (to cut down on duplicates). I have always thought I should write an article on it as it has been quite effective at cutting down on dups.
>
>Revisiting it now and trying to improve one search. For background, this search works fine. I call it "first initial search".
>
>table contains: S. Stallone
>user enters: Sylvester Stallone
>
>class preps the search value by taking the first letter of the firstname and adds a period to do the search (lastname = "Stallone" and firstname = "S."). (of course, all searches case insensitive) Works fine.
>
>Where I am a bit stuck is trying to get an *optimized* search for the reverse of this:
>
>table contains: Scott Peck
>user enters: M. Scott Peck
>
>What I did (2001 vintage version): took just the first character of the firstname and searched for that (lastname = "Peck" and firstname = "M) - which actually does not catch the above.
>
>Reviewing the table though, I realize that most of the entries are like the above and so I need to change this.
>
>I could just strip the initial and do the search but I am wondering if there are other options - I have not used the LIKE operator in SQL. Is it optimized or can anyone suggest other fast ways to do this?
>
>Albert

Albert

You may consider to use an inverted index. It will add some extra work for data updating, but will allow fast and optimized queries.

This code is long only because it includes sample data of 500 names, but other than that it presents a simple inverted index implementation.

LOCAL SampleSource AS String
LOCAL ARRAY SampleData(1)

TEXT TO m.SampleSource NOSHOW
Leah Paterson
Sally Quinn
Carolyn Dickens
Trevor Ince
Matt Henderson
Alan Short
Sebastian Hardacre
Lucas Black
Christopher Forsyth
Keith Walsh
Yvonne Mitchell
Jan Hudson
Phil Lambert
Benjamin Hill
Olivia Avery
Wendy James
Andrew Glover
Jennifer Metcalfe
Joshua Dyer
Oliver Hart
Leonard Roberts
Theresa Anderson
Hannah Terry
Ella Mills
Robert Hill
Felicity Skinner
Jonathan Paterson
Abigail Hughes
Diana Randall
Diana Baker
Bernadette Fraser
Michael Kelly
Trevor Clarkson
Gordon Smith
Paul Mathis
Maria Turner
Gabrielle Paterson
Amanda Gray
Connor Gill
Boris Rampling
Charles Wilkins
Julian Buckland
Natalie Sharp
Neil Baker
Mary Cornish
Piers Russell
Deirdre Duncan
Brandon Taylor
William Burgess
Claire Abraham
Warren Lawrence
Jake Duncan
Dan Parr
Alexandra Clark
Lisa Brown
Bella Davidson
Ava Mackay
Karen Greene
Samantha Vance
Sophie Metcalfe
Grace Vaughan
Sebastian Dowd
Donna Butler
Alan Martin
Ava Cameron
Virginia Piper
Leonard Mathis
Anna Miller
Rachel May
Kylie Hill
Victoria Dowd
Megan Sharp
Phil McGrath
Robert Forsyth
Felicity Churchill
Austin Kerr
Anne Knox
Sebastian Coleman
Ruth Langdon
Ella Kelly
Leonard Lee
Rebecca Turner
Deirdre Mathis
Sebastian Morrison
Nathan Hunter
Michelle Taylor
Stephen Piper
Christopher Russell
Jacob Black
Dorothy Allan
Alan Wallace
Connor Hart
Penelope Knox
Jacob Scott
Megan White
Austin Skinner
Sonia Paige
Brandon Robertson
Nicola Marshall
Charles Cameron
Alexander Marshall
Max White
Jack Welch
Kevin MacLeod
Nathan Carr
Wendy Springer
Sebastian Walsh
Jacob Fisher
Isaac Stewart
Jasmine Lewis
Tracey Morrison
Sally Graham
Lillian Langdon
Penelope Rees
Christopher Ross
Ryan Ellison
Audrey Marshall
Yvonne Cameron
Neil McLean
Sarah Newman
Heather Clarkson
Colin Hunter
Blake Bower
Adam Hunter
Karen Turner
Alexander Sharp
Ryan Nolan
Bella May
Piers Sutherland
Katherine Marshall
Andrew Springer
Paul Gibson
Jasmine Dyer
Jane Brown
Carol Hudson
Diana Ball
Megan Ross
Liam Hunter
Diana Mathis
Leah Duncan
Penelope Anderson
Liam Parsons
Abigail MacLeod
Adrian Oliver
Melanie Clarkson
Nicholas Avery
Una Martin
Charles Sharp
Piers Parsons
Alison Terry
Julia MacDonald
Fiona Vance
Thomas Lambert
Nicola Smith
James Jones
Tracey Dyer
Chloe Wallace
Yvonne Cornish
Stephen Lewis
Sonia Lee
Jane Randall
Sue Berry
Olivia Thomson
Claire Lewis
Oliver Glover
Edward Marshall
Mary Oliver
Lillian Springer
Eric MacDonald
Dominic Dowd
Andrew Sutherland
John McDonald
Owen Glover
Adam Hemmings
Rebecca Churchill
Nathan MacDonald
Carolyn Wilkins
Brian Avery
Austin Greene
Bella Terry
Neil Terry
David Reid
Sonia Butler
Michael Graham
Robert Arnold
Connor Poole
Phil Ball
Adrian Gibson
Victoria Pullman
Audrey Hart
Warren Piper
Rose Glover
Sophie Gray
Diana Harris
Victor Nolan
Joseph Knox
Carol King
Caroline Greene
Ava Buckland
Nicola Mills
Trevor Parr
Abigail Ince
Andrew Oliver
Carolyn Johnston
Rose Baker
Natalie Bond
Carol Stewart
Justin Anderson
Victoria Anderson
Sally Hart
Kylie King
Andrew Ogden
Una Parr
Evan Welch
Anna Lee
Lily Nolan
Brian Black
Wendy Dickens
Sam Arnold
Andrea Parr
Yvonne Alsop
Joe Nash
Keith Thomson
Simon Gibson
Victoria Sutherland
Isaac Davidson
Neil Oliver
Michelle Mackay
Brian Slater
Tim North
Gabrielle Alsop
Keith Alsop
Carl Gray
Audrey Cameron
Richard Gill
Diana Davies
Sebastian MacLeod
Dylan Allan
Thomas Dickens
Lisa Pullman
Owen McDonald
Nathan Abraham
Christopher Arnold
Keith Grant
Felicity Lawrence
Jake McDonald
Sally Reid
Anna May
Andrew Parsons
Penelope Jones
Theresa Rees
Jason Hughes
Alexander Peake
Carl May
Diana Lawrence
Sean Howard
Karen Arnold
Jonathan Churchill
Cameron Manning
Ava Mathis
Christopher Greene
Kylie Butler
Kimberly Arnold
Una Henderson
Anthony Ross
Emma Miller
Donna Blake
Rose Thomson
Felicity Walsh
Joe Sanderson
Penelope Morrison
Warren James
Melanie Lawrence
Alison Fraser
Ryan Hughes
Alexander Avery
Neil Howard
Chloe Anderson
Virginia Hart
Zoe Rampling
Peter Kerr
Madeleine Hill
Harry Bower
Jonathan Murray
Sonia Thomson
Rebecca Miller
Victoria Ross
Kevin Hamilton
Sonia Henderson
Maria Davies
Richard Blake
Colin Russell
Amelia Vance
Lily Davies
Madeleine Lewis
Keith Harris
Owen Marshall
Una Kelly
Olivia Miller
Jacob Tucker
Virginia Ross
Jan Wright
Ruth Peake
Fiona Avery
Angela Young
Felicity Davies
Anna Hardacre
Jessica Clarkson
Sarah Abraham
Joshua Rees
Tim Underwood
Tim Johnston
Victor Vaughan
Sue Anderson
Lisa Murray
Molly Mitchell
Christopher Glover
Caroline Payne
Sally Jones
Jane Nash
Dylan Dickens
Carol Slater
Jason Metcalfe
Piers Buckland
Gavin Bailey
Penelope Clark
Melanie Graham
Nathan Duncan
Kylie Chapman
Leonard Hughes
Stewart Murray
Amy Roberts
Stewart Tucker
William Black
Kevin Paige
Richard North
Jane Avery
Rachel Mills
Molly Wright
Simon Greene
Jake Clarkson
Michael Hemmings
Sebastian Gill
Phil Randall
Colin Mitchell
Tim Dyer
Kylie Gill
Caroline McDonald
Sue Howard
Adam Jackson
Kevin Wallace
Theresa Gill
Hannah Marshall
Lucas Gill
Rose Taylor
Zoe Anderson
Amanda Powell
Brandon Peters
Benjamin Arnold
Dylan Hart
William Welch
Adam Mackenzie
Michelle Martin
Vanessa Hamilton
Cameron Parr
Megan Dowd
Jonathan Abraham
Isaac Watson
Dominic Wright
Jessica Wilkins
Owen Ogden
Faith Mathis
Steven Fisher
Stewart Mathis
Joshua May
Bernadette Terry
Jason Watson
Lucas Duncan
Michelle Brown
Adam Bell
Tim Mitchell
Maria Hart
Gordon Graham
Amelia Lawrence
Justin Grant
Michelle White
Jonathan Newman
Emily Quinn
Yvonne Young
Wendy Gray
William Buckland
Neil Avery
Lily Watson
Sally Ellison
Faith Cameron
Emma Chapman
Theresa Randall
Kimberly Glover
Victor Rees
Alexander Mills
Colin Hardacre
Ian Hill
Robert Vaughan
Jonathan Welch
Amelia Fisher
Julia Poole
William Short
Dorothy McDonald
Charles Greene
David Dyer
Pippa Mitchell
Christopher Edmunds
Joan Grant
Peter Scott
Richard Ferguson
Carolyn Mackay
Mary Avery
Nicholas Ellison
Molly Rutherford
Alexander Vance
Amanda Baker
Peter Berry
Megan Mills
Emily Davidson
Sally Kerr
Pippa Graham
Gordon May
Mary Mackenzie
Luke Coleman
James Martin
Felicity Anderson
Cameron McGrath
Sue Hodges
Rebecca Hardacre
Sally Young
Gavin Skinner
Anna Howard
Connor Gibson
Lauren Forsyth
Edward Wallace
Grace Rampling
Olivia Fisher
Sebastian Lawrence
James Howard
Nicola Hudson
Felicity Payne
Jason Jones
Michelle Lewis
Richard Walker
Leonard Forsyth
Alexander Newman
Julian Rampling
Adam MacLeod
Ryan Murray
Richard Bond
Steven Coleman
Liam Morgan
Tracey Oliver
Dominic Henderson
Megan Burgess
Dorothy Davies
Benjamin Randall
John Russell
Ryan Duncan
Ruth Forsyth
Dominic Johnston
Faith Davidson
Richard Ogden
Angela Hudson
Gordon Coleman
Benjamin Mackay
Wanda Morrison
Grace Piper
Stewart Nolan
Jack Vaughan
Jan Avery
Peter Campbell
Carol Terry
Eric Clarkson
Anthony Mitchell
Theresa North
Benjamin Bell
Lillian Arnold
Stewart Welch
Thomas Bond
Tracey Fisher
James Clarkson
Lillian Dowd
Sophie Avery
Michael McGrath
Frank Cornish
Brian North
Julian James
Sean Vaughan
Oliver Morrison
Nathan Fraser
Nathan Arnold
Rachel Newman
Evan Hardacre
Amanda Abraham
ENDTEXT

* the real data table with full names
CREATE CURSOR Names (IdName Integer, FullName Varchar(200))
INDEX ON IdName TAG IdName

* the parts of name as extracted from the real table
CREATE CURSOR NamesParts (IdNamePart Integer, NamePart Char(30))
INDEX ON IdNamePart TAG IdNamePart
INDEX ON NamePart TAG NamePart COLLATE "General"

* the inverted index file
CREATE CURSOR Inverted (IdName Integer, IdNamePart Integer)
INDEX ON IdName TAG IdName
INDEX ON IdNamePart TAG IdNamePart

LOCAL LoopIndex AS Integer
LOCAL WordIndex AS Integer
LOCAL Word AS String
LOCAL IdNamePart AS Integer

* populate the real table
FOR m.LoopIndex = 1 TO ALINES(m.SampleData, m.SampleSource)

	INSERT INTO Names (IdName, FullName) VALUES (m.LoopIndex, m.SampleData(m.LoopIndex))

	* get the names parts for each name
	FOR m.WordIndex = 1 TO GETWORDCOUNT(m.SampleData(m.LoopIndex))

		* fetch the name part identifier
		m.Word = UPPER(GETWORDNUM(m.SampleData(m.LoopIndex), m.WordIndex))
		IF !SEEK(m.Word, "NamesParts", "NamePart")
			m.IdNamePart = RECCOUNT("NamesParts") + 1
			INSERT INTO NamesParts (IdNamePart, NamePart) VALUES (m.IdNamePart, m.Word)
		ELSE
			m.IdNamePart = NamesParts.IdNamePart
		ENDIF

		* for each part name, add to the inverted index 
		INSERT INTO Inverted (IdName, IdNamePart) VALUES (m.LoopIndex, m.IdNamePart)
	ENDFOR
ENDFOR

LOCAL SearchTest AS String

DO WHILE VARTYPE(m.SearchTest) == "L" OR !EMPTY(m.SearchTest)

	m.SearchTest = INPUTBOX("Enter names to search for", "Test")
	IF !EMPTY(m.SearchTest)

		* the collection of search terms
		CREATE CURSOR Search (PartIndex Integer, NamePart Varchar(30))

		*your search preprocessor should handle all of its rules here
		* this version simple considers any point as a wildcard signal
		FOR m.WordIndex = 1 TO GETWORDCOUNT(m.SearchTest)
			INSERT INTO Search VALUES (m.WordIndex, CHRTRAN(UPPER(GETWORDNUM(m.SearchTest, m.WordIndex)), '.', '%'))
		ENDFOR

		* this query demands that ALL terms be found in each row
		SELECT COUNT(DISTINCT sch.PartIndex) AS Matches, nam.FullName ;
			FROM Names nam ;
				INNER JOIN Inverted inv ON inv.idname = nam.idname ;
				INNER JOIN NamesParts np ON np.idnamepart = inv.idnamepart ;
				INNER JOIN Search sch ON np.namepart LIKE sch.namepart ;
				GROUP BY nam.IdName, nam.FullName ;
				HAVING Matches = (RECCOUNT("Search")) ;
				INTO CURSOR Result

		BROWSE
	ENDIF
ENDDO