Solved

SQL query

Posted on 2011-09-05
23
265 Views
Last Modified: 2012-05-12
Dear All:

Please advise how should I write the SQL query  based on follow condition.

Database:
L321M1233
L1M123M19
A32-32
L21N21J12
12320-21
EC135-02


Input:
Part number  L321M1233 for aircraft EC135.

How can search the user input and get the result "L321M1233" only?  I did using "MATCH" but it return "L321M1233" and "EC135-02"

Thank you.
0
Comment
Question by:simonlai
  • 6
  • 5
  • 5
  • +3
23 Comments
 
LVL 4

Expert Comment

by:Ara-
ID: 36486615
SELECT [column] FROM [database] WHERE [column] = "L321M1233"

Something like that?
0
 
LVL 1

Author Comment

by:simonlai
ID: 36486725
Ara:

thanks but this only if user input "L321M1233".

I need if user input "Part number  L321M1233 for aircraft EC135." it also return result "L321M1233".

and i cannot split the input to word by word due to user may input 1000+ word.

Thank you,
0
 
LVL 4

Expert Comment

by:Ara-
ID: 36486876
Try [column] = "%L321M1233%"
or
[column] LIKE "%L321M1233%"
0
 
LVL 4

Expert Comment

by:Ara-
ID: 36486888
SELECT [column] FROM [database] WHERE [column] LIKE "%L321M1233%"

should do it
0
 
LVL 1

Author Comment

by:simonlai
ID: 36486990
Ara:

Thanks. But only partially solve the problem.

For example:
I got this data in database
L321M1233
L1M123M19
A32-32
L21N21J12
12320-21
EC135-02

I want to get the result "L321M1233" if user enter "L321M1233 for aircraft EC135". I can not force user enter "L321M1233" only.

I did used "MATCH AGAINST" but it return a result with "L321M1233" & "EC135-02". But for my case only first one is correct.

Thank you.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36487102
You would somehow need to indicate which word in the search string you want to query and split that out in the query otherwise how do you know which word to search for.

One way or another you need to refine the input from the user by cutting down the search string or indicating which word to search for.

HTH
0
 
LVL 1

Author Comment

by:simonlai
ID: 36487308
Hi GLParker:

Yes. at this moment, this only solution can think of.

But if user upload a file then process time will be a problem.

Thanks.
0
 
LVL 4

Expert Comment

by:Ara-
ID: 36487507
Ah, yes of course. I can't think of another way either than to split the words (by whitespace?) and compare them individually. Or if it's always the first word you can split out only the first word.

Is there some other reason for the users to specify the aircraft type? Since it isn't really relevant to your search?

How many database entries are there?
0
 
LVL 1

Accepted Solution

by:
GenerLopez earned 350 total points
ID: 36490203
The only way (to me) to do this is tagging the input text.
So , for example, ask your users to tag the codes with < for beginning and > for ending, showing something like this:
Part number  <L321M1233> for aircraft <EC135>.
Then, write a stored procedure to parse text and do the query appropiately.
It seems *very* complex to write a single query to solve it.
Here is the code that does the trick:

1) two functions to parse < and >:
FUNCTION EXTRACT_CODE (IN :ISTRING VARCHAR(1000000)) RETURNS VARCHAR -- extracts the code between < and >
BEGIN
      RETURN SUBSTRING(:"ISTRING" FROM POSITION('<' IN :"ISTRING")+1 FOR POSITION('>' IN :"ISTRING")-POSITION('<' IN :"ISTRING")-1);
END
FUNCTION PARSE_RIGHT (IN :ISTRING VARCHAR(1000000)) RETURNS VARCHAR -- extracts the remaining text
BEGIN
      RETURN SUBSTRING(:"ISTRING" FROM POSITION('>' IN :"ISTRING")+1 FOR CHARACTER_LENGTH(:"ISTRING")-POSITION('>' IN :"ISTRING"));
END
2) A procedure building a temp table with the codes parsed
PROCEDURE PARSE (IN :ISTRING VARCHAR(100000))
BEGIN
CREATE TABLE "_SYSTEM"."TEMP"("THE_VALUE" VARCHAR(1000)); -- assumes ther is no "temp" table before
DECLARE "TEXT" VARCHAR(100000);
DECLARE "VCODE" VARCHAR(100000);
SET :"TEXT"=:"ISTRING"; -- init to input string
SET :"VCODE"=CASE WHEN CHARACTER_LENGTH(:"TEXT")>0 THEN "_SYSTEM"."EXTRACT_CODE"(:"TEXT") ELSE '' END; -- extracts the first "legal" code
-- using "case" construct to force blank text and avoid runtime errors
WHILE :"VCODE"<>''   -- looping until no more codes
      SET :"TEXT"=CASE WHEN CHARACTER_LENGTH(:"TEXT")>0 THEN "_SYSTEM"."PARSE_RIGHT"(:"TEXT") ELSE '' END; -- selects the remainig text from last code found
      INSERT INTO "_SYSTEM"."TEMP"("THE_VALUE") VALUES :"VCODE"; -- records the code into temp
      SET :"VCODE"=CASE WHEN CHARACTER_LENGTH(:"TEXT")>0 THEN "_SYSTEM"."EXTRACT_CODE"(:"TEXT") ELSE '' END; -- new code
END WHILE; -- end of loop
-- Result: Temp table with codes found
END

3) write the following on your program or SQL manager of your database:
CALL  "_SYSTEM"."PARSE"('Part number  <L321M1233> for aircraft <EC135>'); -- calling the parse procedure. / output temp file
-- assume that table containing real records is called "CODES" and "code" is the field containig the codes.
SELECT * FROM "_SYSTEM"."CODES" WHERE "CODE" IN (SELECT "THE_VALUE" FROM "_SYSTEM"."TEMP"); -- select final select showing the records matching the finded codes
DROP TABLE TEMP CASCADE; -- erase temp table

It should works, I've tried.
FYI: "_SYSTEM" is the name of the schema where the tables are.
There may be some differences depending you database flavour.
This code is strictly SQL92 compliant.
Hope this helps.
Regards
Gener
0
 
LVL 40

Expert Comment

by:Sharath
ID: 36491511
Is the user input always in the format xxxx for aircraft yyyy ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:simonlai
ID: 36492880
Ara:

it was close to 30K entities. This just my example.User may enter some other input relate with part number.

GenerLopez:

I am trying not to set the rules for user to make the system easy for user.

Sharath_123:
Yes and maybe some other keyword.

0
 
LVL 4

Expert Comment

by:Ara-
ID: 36493726
For doing exactly what you want; that is keeping it simple for the user, I really think your only option is to split the input string to words.

If there were few entries in the database you could consider doing it the other way around; that is read each entry from the database and check if the input string contains that enty, but it wouldn't be a scalable solution and to 30K entries not a good idea..
0
 
LVL 1

Assisted Solution

by:GenerLopez
GenerLopez earned 350 total points
ID: 36494359
If it's not acceptable to tag the codes, There is  a single select that do the trick **** but will find exact matches ****:
select * from [table] where POSITION ([code_field] IN 'Part number  L321M1233 for aircraft EC135-02') >0

Open in new window

Your question seems to say that also partial matches are allowed ( "EC135" in text matches with  "EC135-02" code)
If this is a requirement  then there is no other option: you must separate every word and compare it against the real codes using LIKE to allow partial matches.

The solution is still valid, the difference is that in TEMP table there were all the words and SELECT will find every real code comparing them to all the text words.

You only need
a) substitute  > for space on PARSE_RIGHT
b) substitute
RETURN SUBSTRING(:"ISTRING" FROM POSITION('<' IN :"ISTRING")+1 FOR POSITION('>' IN :"ISTRING")-POSITION('<' IN :"ISTRING")-1); 

Open in new window

for
RETURN SUBSTRING(:"ISTRING" FROM 1 FOR POSITION(' ' IN :"ISTRING")-1);

Open in new window

on EXTRACT_CODE
and: don't forget to **** TRIM SPACES FROM :TEXT ***** before extracting code to eliminate trailing spaces.

Hope this helps
Gener
0
 
LVL 40

Expert Comment

by:Sharath
ID: 36497881
<< Is the user input always in the format xxxx for aircraft yyyy ? >>

<< Sharath_123:
Yes and maybe some other keyword. >>

So, the search string is always the first word (xxxx) in your search criteria. right?
0
 
LVL 1

Author Comment

by:simonlai
ID: 36528758
not...user can enter any string or word.


in fact the user will upload a file (which export from other system) to the system and the system will read the content and search to database. (the content is not fix)

Thank you.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36940975
I suggest close with no answer
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 36940983
closing:
My stored procedure proposal was tested and works, both approaches.
But the question is not fully explained, so don't know if partial codes should be allowed.
Even so, I 've suggested a (not fully  tested) solution.
No feedback, so...

Gener
0
 
LVL 1

Author Comment

by:simonlai
ID: 37029980
I've requested that this question be deleted for the following reason:

Not solution advised....
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 37029981
A solution was provided, and I spent much time working to build and test it. only in the hope of helping simonlai
There are no answers or opinions simonlai

What does "Not solution advised ..." mean?
SImonlai has reviewed and tested my proposals (both) or not?

Therefore, this means that:
   You can send a question, sit back and wait, a few colleagues spend their time answering your question (trying to help you), in the meanwhile, nothing is done, probably the problem disappears itself, or you find a solution by yourself and then you close with a mere "Not solution advised ...", without explaining why the solutions received do not fit the need, no thanks to anyone ...

If there  is a moderator (I doubt) I think that just "Not solution advised ... ..." should not be acceptable, must be followed by the reasons that the solutions do not fit, or any other explanation about...

If not, answering the questions is a waste of time.
This is not fair.

It's proof that "if this does not cost anything to me, this has no value ...".
This was my first (and last) contribution to this community.
Regards
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 37031205
I suspect we haven't fully understood the problem.

As I understand the issue

The user runs some program that produces a file.  The file has text in it with no strict format required.
Apparently the input stream (file) cannot be changed so any method of tagging or otherwise identifying the search criteria is not a solution.
The text includes aircraft identifiers in some form.  
I also assume the aircraft names are entered accurately though no guarantee EC105 or EC105-02 is entered not EC-105, EC 105, EC105 02.

 
Example text
   Some random words EC105 Other random words
   More extra text L321M1233 etc 
   until the end of the file which may be 30K or more A32

From this you need to find all records in the database for  *EC105*, *L321M1233*, and *A32*

Open in new window


A human reading the file could relatively easily highlight the search text and pull out the search terms.

If there was a requirement that the full database value be included then the search could be done the other direction
DatabaseField IN TEXT > 0

In that case EC105-02 would not be seen as a match for EC105 nor would A32-32 match A32

Considering the potential length of the input file parsing it into words and searching for each word might be too time consuming.  The text I provided has 25 "words" which each would need to be used in the search.

As such there are parts of the answer there but not a complete answer.  I am not sure if there is even a full answer in combining the comments.

mlmcc
0
 
LVL 1

Expert Comment

by:GenerLopez
ID: 37031638
mlmcc,
I fully understands the question.
And I provided solution for tagged (first post) and non tagged text (second post),
For complet codes (first and second post) and for partial codes (last comment on second post).

Parsing works
Time consuming issues depends on the number of files beeing processed simultaneously and his length, of course, but no input was given about the time limit criticism nor the number of files to be processed at the same time.
So the solution is still valid.
Unfortunately I've deleted the example made for test the solution so I can't do stress tests now.
I can't waste more time, I still think that this tests should be done by simonlai, not by me.

For me, this question is closed,

 I could try to give advice, but I can't do the others work.
So... do what you want
regards
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now