Solved

SQL Searching

Posted on 2016-10-07
3
70 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 28

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 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: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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 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