# Miscellaneous > SQL Scripts >  Query to search text in string

## mac300

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

----------


## PABluesMan

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.

----------


## mac300

Thanks PABluesMan for the response.

However, there are 2 problems I can think of:
1. What you suggest is possible of course, but it will mean to basically change the table, so that instead of 1 column with the info I would have 2 - 1 for the cityID and 1 for the rest of the address. I will then need to go over each row and update the info - or do it using a query which is more or less what I'm looking for anyway.
2. What I gave was only an example. It is not the actual table format I have but similar. What I called the Student table is actually a big table that rows are added everyday, about 10-20 rows an hour. So, taking into acount what I mentioned above that means a lot of work.

So, going back to the example, I need a query that will search for the entry [City].[city] in [Student].[address]. To make it easier, I want the output to be 3 columns: [studentID], [cityID], [address].

Can you give me such a query?... please  :Wink:

----------


## PABluesMan

One way to do this would be to add a computed column to the [Student] table. This column would have a formula that would parse the city name out of the address field, and you could then use this to join to the city table.

Otherwise, you're looking at a VERY expensive query.

----------


## mac300

Ok, so if I do as you suggest, how would you write the formula/query?

----------


## PABluesMan

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!

----------


## neo_morphius

Hi check this out if it works

declare 
v_id city.cityID%TYPE;
v_city city.city%TYPE;
v_sid student.studentID%TYPE;
begin


for s_rec in (select * from student )
loop 
for c_rec in (select * from city )
loop 
v_sid := s_rec.studentID;
v_city := '%'||c_rec.city||'%';

select min(cityid) into v_id from student s, city c where  trim(city) = trim(c_rec.city) and s_rec.addresss like v_city ;

if v_id is not null then
dbms_output.put_line( s_rec.studentid||chr(9)||s_rec.s_name ||chr(9)||v_id ||chr(9)||c_rec.city);
end if;
end loop;
end loop;
--exception
--When others then
		-- dbms_output.put_line( 'Exception_occured at ID ' || v_sid);
end;

----------


## ld_be

Hello


Are you still looking for a query?

SELECT studentID, name, address, cityID 
FROM Student, City 
WHERE address LIKE CONCAT('%', city, '%') 
ORDER BY studentID

In this case, you dont see the students with no matching cityID.

With the following query the same result.

SELECT studentID, name, address, cityID 
FROM Student 
INNER JOIN City 
    ON address LIKE CONCAT('%', city, '%') 
ORDER BY studentID

When you still want to see all the students, also with no cityID, change INNER into LEFT.

SELECT studentID, name, address, cityID 
FROM Student 
LEFT JOIN City 
    ON address LIKE CONCAT('%', city, '%') 
ORDER BY studentID

If address always begin with city then you can change CONCAT('%', city, '%') into CONCAT(city, '%')

I hope this will help you.

----------

