Break down a string into individual parts to search a database

[buickgn]

Thank you so much Mark! That is exactly what I needed! Now I just need to figure out how to query each string using SQL. Any thoughts/ideas?

[Mark_Yedinak]

---

@buickgn wrote:

Thank you so much Mark! That is exactly what I needed! Now I just need to figure out how to query each string using SQL. Any thoughts/ideas?

---

This one is a bit harder to answer not knowing your DB schema. If you have the ability I recommend defining stored procedures for your interface to the DB. Stored procedures generally will perform better than straight SQL statements and help to define a clean interface to the DB.

In regards to your question it will depend on whether you want to get one set of data for your query or perform the queries on the tables individually. Based on what you have said I think a single query which gives you your data would be preferred. This will result in a query contain multiple WHERE clauses as well as tbale JOINs. Beoyond that without the schema there is not much more I can offer.

A single query would look something like:

SELECT * from T1 JOIN T2 WHERE T1.fi=<SN part from regex> AND T2.f1=<Another SN part>;

[buickgn]

I'm not sure this would help but it is a single database with 8 tables. Each table corresponds to a number that the VI you showed me produces.

[buickgn]

How would I make the "Submatch" for each of the 8 results from the "Match Regular Expression" function useable in querying the database? Since they are all indicators, I'm not sure how to do that either... Thanks again for all the help

[Mark_Yedinak]

You simply wire the output of the desired submatch to the input of whatever you need. In your case it would most likely be a format string. Depending on what VIs of the DB toolkit you use it may also be a build array since some of the DB toolkit VIs take arrays as input.

[buickgn]

So something like this as a starting point?

[nyc_(is_out_of_here)]

Looks like you are another user who should take SQL 101   http://ycmi.med.yale.edu/nadkarni/access_course/SQL_Frame.htm

[Mark_Yedinak]

Sort of but I don't think the way you have it wired would work. From the picture it looks liked you have effectively concated all the component parts of teh serial number back into a string with only the '.' missing. THis looks like it is wired into the table name of the select VI. It is dificult to tell where your SQL stuff is wired.

I suggest you do a little reading up on SQL in general. This will help you quite a bit. Beyond knowing your schema I cannot give you any more detailed advice. Yes, I know portions of the serial number are keys in separate tables. But in order to write SQL one needs to know table names, column names and their relationships.

[buickgn]

Ok. I'll read up on it. I thought I could get the wiring down then work on the SQL command, but from what you are saying getting SQL down will help me figure out how/where to wire?

[Mark_Yedinak]

Yes, a basic understanding of SQL will go a long way in helping you actually use the DB VIs. You also need to know some specifics about the DB you are working with. This is one reason I highly recommend using stored procedures. If you are not responsible for the DB itself you may want to ask the folks who are to provide some stored procedures for interacting with the DB. They tend to be much easier to understand and use as well as hide all the SQL and schema specifcs.