Solved

SQL Searching

Posted on 2016-10-07
3
45 Views
Last Modified: 2016-10-10
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
Comment
Question by:jdr0606
3 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41834587
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41834670
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41835483
--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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now