Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Searching

Posted on 2016-10-07
3
Medium Priority
?
94 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 35

Expert Comment

by:Pawan Kumar
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 49

Accepted Solution

by:
PortletPaul earned 2000 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 43

Expert Comment

by:Eugene Z
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

877 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