CODEWITHSUNDEEP
sqlsql-serversql-like
J.S.Orris
J.S.Orris

Reputation: 4831

SQL: How Do I Use LIKE Keyword on the Column Rather than Just One Value?

The following are 2 queries I tried without any success of getting the results I want:

SELECT H.Type, H.SN, R.Type, R.SN  
FROM Hours H 
INNER JOIN Repair R
ON H.SN = R.SN AND H.Type LIKE '%' + R.Type

AND

SELECT H.Type, H.SN, R.Type, R.SN  
FROM Hours H 
INNER JOIN Repair R
ON H.SN = R.SN AND H.Type LIKE '%R.Type'

How do I use the LIKE keyword for the column rather than one value? I.E I want the H.Type = 424 to be JOINED to R.Type = 424A

EDIT:

The following are some sample results I'm getting after making the recommended adjustments:

---------------------------------------
H.Type    |H.SN    |R.Type   |R.SN    |
---------------------------------------
R99       |270     |EMPTY    |270     |    
R99       |270     |EMPTY    |270     |     
R99       |270     |EMPTY    |270     |     
R99       |270     |EMPTY    |270     |
B134      |427     |EMPTY    |427     |
B134      |427     |EMPTY    |427     |
B134      |427     |EMPTY    |427     |
B134      |427     |EMPTY    |427     |

There should be no empty fields. Why are there?

Upvotes: 0

Views: 68

Answers (2)

crthompson
crthompson

Reputation: 15875

Your first query is correct except you transposed the value needing the wildcard and then just add another percentage symbol.

AND R.Type LIKE '%' + H.Type + '%'

The % is the wildcard so as you had it it would match A424 but not 424A.

With percent symbols on both sides it will match A424A as well as 424A

There is a performance hit for each percent symbol, so if your requirements are as specific as you say you'd only need the percent on the right side of H.Type.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You are doing the comparison backwards. You want:

and R.Type like H.Type + '%'

This will match:

  • R.type = 424A
  • H.type = 424

If H.Type is number, then cast it first:

and R.Type like cast(H.type as varchar(255)) + '%'

Upvotes: 2

Related Questions