Solved

SQL Searching

Posted on 2016-10-07
3
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

739 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