Query to search text in string
I will try to explain what I need by using an example.
I have 2 tables.
1st table (City) has 2 columns: cityID, city
2nd table (Student) has 3 columns: studentID, name, address
The 1st table, in the city column, has names cities.
The 2nd table, in the address column, has the full address - city, street and house number.
I want to create a query, that will result in a table that will have 4 columns - the 3 columns from the Student table, and also the cityID corresponding to that student's address.
For this to happen as I understand it (and here lies my problem), the query needs to take the data from cityID, search in the address column string if it exists and if it does enter the information in the new column.
Now, I only know the basics in SQL, so whoever responds to this if possible give an example, and try to explain what you did exactly and why.
Thanks in advance
RE: Query to search text in string
Okay, so the first thing you have to do is to add a column to your [Student] table containing the CityID. The value stored here is the same as [City].[CityID]. This way you can join the two tables together and always have a definitive link between the two. It also saves you from having to search text fields all the time.
RE: Search text in string
That would be impossible for me to answer here; I would have to be in your database to do it. However, here are a couple of guidelines to make things easier going forward:
1) You're probably going to have to scrub the data more or less manually, to get it into an easily parsed form.
2) You're going to have to change your input procedures so that it does the data scrubbing for you going forward.
THEN you can write your parsing routine.
Hope this helps!