Solved

Question about function PATINDEX()

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

738 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