?
Solved

SQL ON PERVASIVE SERVER  SEARCHING FOR TEXT IN 2 FIELDS BASED ON COMPARED

Posted on 2011-10-13
3
Medium Priority
?
333 Views
Last Modified: 2012-05-12
I have 2 keys in Pervasive database
first keyfield could contain like following sample
'ABRAHAM LINCOLN'
second keyfield  could contain like following
CITY OF ABRAHAM
ABRAHAM  STREET
NEXT DOOR IS ABRAHAM
etc
     
what is syntax if I need to search on word ABRAHAM from first field into the second keyfield
0
Comment
Question by:BIAPRO
  • 2
3 Comments
 
LVL 29

Assisted Solution

by:Bill Bach
Bill Bach earned 2000 total points
ID: 36961528
There is no direct syntax for this. You'll need to use string parsing (MID, LEFT, RIGHT) along with IF to pull apart a word on the first field, then use SUBSTRING into the second field. Then, you'll need to repeat for the second word, third word, etc.
No matter your solution, it will be CPU-intensive and slow.
0
 
LVL 29

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 36961721
The more I think about this, the more I think that writing a stored procedure, then looking at each candidate record, makes more sense.

To try to do it with pure SQL will be ugly and not very flexible.  You could, for example, find the FIRST NAME from the first field with a construct like this:

SELECT * FROM Table
WHERE POSITION(RTRIM(LEFT(Field1,POSITION(Field1,' '))),Field2) > 0

This finds the first space in the first field, then grabs the leftmost string (the first word), gets rid of any trailing spaces, then checks to see if this exists in the second field.  Of course, this will find substrings, not sub-words.  To find sub-words, you have to change this to also look for a leading or trailing space.

A stored procedure can use the LOCATE and SUBSTRING functions to essentially parse apart each string into an array of words, then check each word in one field against each word in another.
0
 

Author Closing Comment

by:BIAPRO
ID: 36966664
Thanks again Bill
regards Jack
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question