0

I have two tables like so 

Table A
server_name
start_time
end_time

Table B
id
description

What I want to do is join the two tables together so it looks like this

Table C
server_name
start_time
end_time
id
description

The only way I can make the join is if I search for the server_name in the description.

I have tried using the like function but this doesn't work. It needs to be exactly what is in server_name column in the description

jarlh
  • 42,561
  • 8
  • 45
  • 63
Dean Flaherty
  • 351
  • 1
  • 5
  • 15

1 Answers1

0

Use LIKE:

select *
from tableA ta
join tableB tb on tb.description like '%' + ta.server_name + '%'

The + is SQL Server concatenation. ANSI SQL has ||, and others have concat instead.

Extended version, add space before/after:

select *
from tableA ta
join tableB tb on ' ' + tb.description + ' ' like '% ' + ta.server_name + ' %'

Takes care of Matt Gibson's test/greatest example too.

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Although, of course, if `ta.server_name` is "test" and the `tb.description` is "The greatest server in the world!" this will match, so whether this will work perfectly depends on what kind of stuff is actually in `description`. – Matt Gibson Feb 19 '16 at 11:30
  • I am using SQL server so I have tried + instead of || – Dean Flaherty Feb 19 '16 at 11:41
  • Matt - I know that will match, I'm just trying to get some matches in first. After running the query above it is 10 minutes and counting and there is on a few 100 rows of data. – Dean Flaherty Feb 19 '16 at 11:43
  • 1
    @DeanFlaherty Searching with `LIKE` with a wildcard at the *beginning* of the string is never going to be that fast if you have lots of data. It can't use an index even if you've got one, because indexes work by sorting things into order, and a match on '%whatever%' could obviously be at any position in the index. (It's not a ["search argument", or a SARGable query](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable), in SQL Server terms.) – Matt Gibson Feb 19 '16 at 11:51