Results 1 to 8 of 8

Thread: Query to search text in string

  1. #1
    Join Date
    Mar 2008
    Posts
    3

    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

  2. #2
    Join Date
    Mar 2008
    Posts
    11

    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.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    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

  4. #4
    Join Date
    Mar 2008
    Posts
    11

    RE: Text search

    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.

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    Ok, so if I do as you suggest, how would you write the formula/query?

  6. #6
    Join Date
    Mar 2008
    Posts
    11

    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!

  7. #7
    Join Date
    Apr 2008
    Posts
    1
    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;

  8. #8
    Join Date
    Jul 2008
    Location
    Belgium
    Posts
    17
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •