Solved

Question about function PATINDEX()

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

17 Experts available now in Live!

Get 1:1 Help Now