Solved

Question about function PATINDEX()

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now