[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Question about function PATINDEX()

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
ferbit
Asked:
ferbit
1 Solution
 
ralmadaCommented:
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
 
cyberkiwiCommented:
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
 
ferbitAuthor Commented:
thank you so much!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now