Searching Keywords in a Query String

[Saarang84]

Hi,

I've got a set of field names in a column in an Excel sheet.

I need to search a query string extracted from a database (input in a textbox) for these set of field names (I may also add new keywords to this list) whether they are used in the query. Search needs to be done on the input query string (which may be more than 255 characters in length) and return an output msg whether any of the keywords are found in the query string.



Regards
Sarang

[dilipandey]

Hi Sarang,

Provide a sample query string in which column A values to be searched... thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Saarang84]

Here's an example query :

SELECT X.ACCT_TYPE, X.ACCT_NUM, X.PLSTC_NUM, Y.CURR_ACCT_NUM FROM ACCOUNT_MASTER X LEFT OUTER JOIN CURRENT_ACCOUNT Y WHERE X.ACCT_ID = '084512215100';

The above example query is a simple one, there also might be complex queries used.

Regards
Sarang

[dilipandey]

Hi Sarang,

See the attached file where I used below formula to check if a keyword is there in query:-

Please Login or Register  to view this content.

Field_Search.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[Saarang84]

Hi,

Thanks for the reply, Dilip.. How can this search be done for all all the keywords in one shot?


Sarang

[dilipandey]

Depends on how you provide those keywords
simple way would be -> enter those keywords in column A, enter your query in yellow cell and drag down the formula in column B. Thanks.



Regards,
DILIPandey
<click on below * if this helps>

[Saarang84]

Dilipji,

There are about 50 different keywords which i need to check whether they exist in the search string.

The query string in which i need to search these keywords are not readily available and need to be extracted from a report. It would be really helpful if I can search all the keywords all in one shot in the query..

All the keywords will be listed in Col A in a worksheet. How can this be done?


Sarang

[dilipandey]

You can have column A full of keywords and after extracting your query enter that in yellow cell and drag down formula in column B to check the results.. simple


Regards,
DILIPandey
<click on below * if this helps>

[Saarang84]

Hi,

Just a small clarification.. Is it possible to modify this search into an exact match?

For example,
Lets say my keyword is ACCT_NUM (in cell A2) and in the string which i input for the search (in cell D1) contains the word BANK_ACCT_NUM.
Currently, the below formula returns a partial match :

Please Login or Register  to view this content.

How can it be modified to check for an exact match?


Sarang

[dilipandey]

If ACCT_NUM is there in string BANK_ACCT_NUM, this means it already a FULL MATCH
I hope you are getting me.. see ACCT_NUM is there (fully found) in string and hence I am saying its full match.

If you need other way around -then its not possible because query string will always be lengthier than keyword - that means query string will always have more characters then a "keyword"


Regards,
DILIPandey
<click on below * if this helps>

[Saarang84]

Dilip,

I would only need to find whether the keyword exists in the query string. At present, it does a full match, but is there a way through some macro code or formula using which an exact match can be made?


Sarang

[dilipandey]

Okay.. for an exact match you can put an extra space in beginning of your key word, see attached file where you'll find " ACCT_NUM" in a2 with an extra space and hence it is not found in d1 but,
Field_Search(1).xlsx

In a15, you'll see "BANK_ACCT_NUM" is fully found in D15.

If you put a space in front of any keyword, then this will treat that as unique and hence formula will give result accordingly.. try this on your own


Regards,
DILIPandey
<click on below * if this helps>