CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gopal
Gopal

Reputation: 11992

How to get a result set of string using like or compare

Using SQL Server 2008

Table 1 varchar(100)

Values1 (Always one row)

123ABC456

Table2 varchar(200)

Values2 (Mulitple Rows)

123ABC456
123ABC456INV1
123ABC456_JULY
JULY123ABC456
0123ABC456
99123ABC456
JULY 123ABC456 INV 1
123JULYABC456
123_ABC456

I want to select exact value1 match from value2 and First 9 characters values2 should be matched from value1. Below for your reference

123ABC456 - Exact Match
123ABC456INV1 - First 9 Character Matched
123ABC456_JULY - First 9 Character Matched
JULY123ABC456 - No Match
0123ABC456 - No Match
99123ABC456 - No Match
JULY 123ABC456 INV 1 - No Match
123JULYABC456 - No Match
123_ABC456 - No Match

Query like

Select * from table2 where values like '% table1 values %'

Expected Output

123ABC456
123ABC456INV1
123ABC456_JULY

Kindly advise and support for the query

Upvotes: 1

Views: 125

Answers (7)

Mihail Shishkov
Mihail Shishkov

Reputation: 15887

Try this one

SELECT t2.Values2 
FROM Table2 t2
INNER JOIN Table1 t1 ON (t2.Values2 LIKE t1.Values1 + '%')

It does not care for Values1's length which is a good thing.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

This is also valid:

SELECT * FROM Table2 WHERE Value2 LIKE (SELECT Value1 FROM Table1) + '%'

Upvotes: 0

T.Z.
T.Z.

Reputation: 2172

You can use SUBSTR for this:

SELECT 
    * 
FROM 
    table1 t1 
JOIN table2 t2 ON SUBSTR(t1.values,1,9) = SUBSTR(t2.values,1,9)

Edit: SUBSTR is more database engine agnostic - so, more portable than LEFT.

Upvotes: 0

Mostafa Darwish
Mostafa Darwish

Reputation: 91

you need to declare the match string first and as you want the same prefix so you should use ='value%'

DECLARE @match NVARCHAR(255)
SELECT @match= (select * from @Table1) + '%';
Select * from @Table2 where value like @match

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use LEFT function.

Returns the left part of a character string with the specified number of characters.

SELECT Table2.Values2 
FROM Table2
JOIN Table1 
ON LEFT(Table2.Values2, 9) = Table1.Values1

Upvotes: 0

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Try this.......

select DISTINCT Table2.Value
from   Table1
join   Table2
    on Table1.value = left(Table2.value,9)

Upvotes: 0

Jamiec
Jamiec

Reputation: 136174

SELECT t2.Values2
FROM Table2 t2
INNER JOIN Table1 t1 
   ON LEFT(t2.Values2,9) = t1.Values1

Upvotes: 0

Related Questions