• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 104
  • Last Modified:

SQL Searching

I'm trying to come up with the correct scenerio to search and find products and like products from a master file.

I have 3 tables
Table #1 is the Master file
It has two fileds that I care about
ID                              Extended
Item123                    Item-123
123Item                    123-Item
Item                           IT-EM
Itm                              ITM

Table #2
It has 2 fields similar to table #1 but the Extended has had the - stripped from the vale
ID                              Extended
Item123                    Item123
123Item                    123Item
Item                           ITEM
Itm                              ITM

The 3 table has the value to search for
ID
123

In this case I want to search table # 2 and return any Extended values that are like '%123%'
Return from table 2 ID's Item123 and 123Item.

Hope this makes sense
0
jdr0606
Asked:
jdr0606
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Here is the entire code you need..

CREATE TABLE tableExtended
(
	ID VARCHAR(100)
	,Extended VARCHAR(100)
)
GO

INSERT INTO tableExtended VALUES
('Item123',                    'Item123'),
('123Item' ,                   '123Item'),
('Item'     ,                      'ITEM'),
('Itm'       ,                       'ITM')
GO

Open in new window


Table2


--

CREATE TABLE TableSearch
(
	ID VARCHAR(100)
)
GO

INSERT INTO TableSearch VALUES 
(123)


--

Open in new window


SOLUTION1


--


SELECT m.ID , m.Extended FROM TableSearch a
CROSS APPLY
(
	SELECT * FROM tableExtended b
	WHERE b.ID LIKE '%' + a.ID +'%'
)m




--

Open in new window


SOLUTION 2

SELECT a.* FROM tableExtended a
CROSS APPLY
(
	SELECT * FROM TableSearch b
	WHERE a.ID LIKE '%' + b.ID +'%'
)m

Open in new window


SOLUTION 3

SELECT a.* FROM tableExtended a
CROSS APPLY
(
	SELECT * FROM TableSearch b
	WHERE CHARINDEX(b.ID, a.ID ,1) > 0
)m

Open in new window



OUTPUT


Output..
Enjoy !!
0
 
PortletPaulfreelancerCommented:
This result:
id      Extended 
------- -------- 
Item123 Item123  
123Item 123Item  

Open in new window

a simple join condition is sufficient for this
select t2.*
from t2
inner join t3 on t2.extended like '%' + t3.id + '%' 

Open in new window

0
 
Eugene ZCommented:
--Return from table 2 ID's Item123 and 123Item.
select table2.* from table2 inner join table1 on table2.id=table1.id
 where 
table2.Extended like '%123%'

Open in new window

--
--in case you really need table3- (EZ- you can use variable, etc.) - it is just an idea

select table2.* from table2 inner join table1 on table2.id=table1.id
 where 
table2.Extended like '%'+(select top 1 cast( id as varchar) from table3 where id=123)+ '%'

Open in new window


ID      Extended
Item123      Item123
123Item      123Item
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now