Solved

Question about function PATINDEX()

Posted on 2010-11-15
3
628 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
[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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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