Post reply

Update through wildcard string search

  • October 18, 2004 at 1:23 pm

    #162519

    Hi there

     

    This is a tricky question (at least to me). Is it possible to update a column in a view from a table through a wildcard string search?

     

    My problem is that I have a table (A) with 640 names and 640 numeric codes corresponding to the names.

     

    In a View (B), with about 6000 records, I have a char column (Owner_name) which sometimes contains names within a text string, and the names can be any of the 640 names from table (A).

     

    What I need is View (B) with an additional column which holds the numeric codes from table (A), if a name from table (A) is found in the Owner_name column in the view.

     

    I hope that this gives any meaning, and I really hope that you can help me.

     

    Regards Joejoe  

  • October 18, 2004 at 2:53 pm

    #527089

    You could try something like this:

    SELECT b.*, a.code FROM a

    INNER JOIN b ON b.text LIKE '%'+a.name+'%'

    Warning: this may give you some duplicates...

    Razvan

  • October 19, 2004 at 2:41 pm

    #527251

    Hi Razvan

    Thanks for your reply

    I tried the query. It runs with out any syntax errors. But it doesn't return any records.

    Then I tried with Like '%Name_of_some_owner%' and this returned all the records in View(B) with the specific owner. But it's a littel bit tedious to do this 640 times. Any suggestions?

    Thanks again for your help Razvan

    Regards Joejoe

  • October 20, 2004 at 12:24 am

    #527271

    Hmm... maybe your Name column (in table A) has the char datatype (I expected it to be varchar). In this case, try:

    SELECT b.*, a.code FROM a 
INNER JOIN b ON b.text LIKE '%'+RTRIM(a.name)+'%'

    Razvan

  • October 20, 2004 at 12:42 pm

    #527353

    1000 thanks Razvan

    It works perfect!!

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply