Solved

Question about function PATINDEX()

Posted on 2010-11-15
3
625 Views
Last Modified: 2012-05-10
Hi, I have a question about function PATINDEX()-in MS SQL Server.
I have a table that contains a field for some string like 'aaa',bbb','ccc'....
How would I use the PatIndex function if I wanted to be able to check if some string
 (like 'abhju aaa') contains any string from first table (like 'aaa' or 'bbb' or 'ccc' or ....)
0
Comment
Question by:ferbit
3 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 34139696
You can use the LIKE operator like below
select a.* from table1 a
inner join table2 b on a.col1 like '%' + b.col1 + '%'

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34141732
LIKE will work as well, but here's an example using PATINDEX
create table #tblWords (word varchar(10))
insert #tblWords select 'aaa'
insert #tblWords select 'bbb'
insert #tblWords select 'ccc'

-- this is a simple query against the input that shows matches (all)
declare @input varchar(max) set @input = 'abhju aaa'
select w.word, @input
from #tblWords w
where PATINDEX('%' + w.word + '%', @input) > 0

-- this word has multiple matches, it will show both
declare @input2 varchar(max) set @input2 = 'bbb abhju aaa'
select w.word, @input2
from #tblWords w
where PATINDEX('%' + w.word + '%', @input2) > 0

-- this word has multiple matches, it will show just one row, if you get no word, then there is no match
declare @input3 varchar(max) set @input3 = 'bbb abhju aaa'
select min(w.word) as JustOneMatch
from #tblWords w
where PATINDEX('%' + w.word + '%', @input3) > 0

Open in new window

0
 

Author Comment

by:ferbit
ID: 34142709
thank you so much!!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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