MS SQL - Joining on two tables with a substringed key in one column

I have a 2 tables I need to join, however on one of the tables I need to extract a key from a varchar field in each row.

Table 1 Description (numeric 18,varchar 4000)

    descriptionid   description 
    1               Blah Blah: Queue 1Blah Blah
    2               foobar:Queue 2
    3               rem:Queue 2 -This is a note
    4               Anotherrow: Queue 3
    5               Something else

Table 2 Queue - (numeric 18, varchar 100)

    queueid queue

    123     Queue 1
    124     Queue 2
    127     Queue 3
    129     Queue 4

So I need to produce the output like so

View 3 Queue-Description (numeric 18, numeric 18)

    descriptionid   queueid
   开发者_开发技巧 1               123
    2               124
    3               124
    4               127
    5               null

So in table 1 row 1, I need to strip out the value Queue1 from the description, verify it is in the queue table, and lookup the queueid.

I am unable to change the structure of tables 1 and 2.

What ways can this be achieved in MSSQL?

What is the most efficient way to do this in SQL - using MSSQL 2005 here.

most efficient way

Well... don't know about that but it is a way.

select T1.descriptionid,
       T2.queueid
from Table1 as T1
  left outer join Table2 as T2
    on T1.description like '%'+T2.queue+'%'

Another way

select T1.descriptionid,
       T2.queueid
from Table1 as T1
  left outer join Table2 as T2
    on charindex(T2.queue, T1.description, 1) > 0

If there are more than one match (see comment by Ed Harper) you can use this to pick the one with the longest match.

select T1.descriptionid,
       T2.queueid
from Table1 as T1
  outer apply ( 
                 select top 1 T3.queueid
                 from Table2 as T3
                 where charindex(T3.queue, T1.description, 1) > 0
                 order by len(T3.queue) desc
              ) as  T2(queueid)

The most efficient way to do this is to add an extra column to your table and insert the extracted the ID from the string. You can do this when rows are added and you can process the existing ones fairly easily. But trying to left join like this will be very slow.

In Sql Server 2005 you can extract your queue string using regex. The Data Extraction section on this page contains an example.

In a stored procedure you can then build an indexed temp table that contains a new column - this allows you to do this without changing the table metadata).

If you can change the table metadata you can:

Trigger the content into another column (on insert).

Or if the information is not needed immediately a daily sql job could extract the information.