quote:

---

Originally posted by nigelrivett

What do you mean by it doesn't work?

Have you checked the data you are searching and searching for?
Does your collation allow distinguishing between accented characters?

I notice your keys have , and | and you are using , to delimit - is that correct?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by visakh16

does that mean you dont want data to be split up based on |?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

so what should be end output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by nigelrivett

When I run that query I get two rows.
What do you get?
Be nice if you told us what the actual issue is as per my first post
This gives two rows as expected on my machine
note: I've change the value in @finsoftKeys to nvarchar

declare @finsoftKeys nvarchar(1000)
select @finsoftKeys = N'Soför/ Destek|Genel Müdürlük|Soför|Soför,Uygulama Gelistirme-Kredi Kartlari ve ATM Bölümü|Genel Müdürlük|Müdür|Yazilim Gelistirme Müdürü'

declare @t table (s nvarchar(1000))
insert @t select N'Soför/ Destek|Genel Müdürlük|Soför|Soför'
insert @t select N'Uygulama Gelistirme-Kredi Kartlari ve ATM Bölümü|Genel Müdürlük|Müdür|Yazilim Gelistirme Müdürü'


;with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@finsoftKeys+',')
union all
select i=j+1, j=charindex(',',@finsoftKeys+',',j+1) from csvtbl
where charindex(',',@finsoftKeys+',',j+1) <> 0
)
,cte as
(
select val = substring(@finsoftKeys,i,j-i)
from csvtbl
)
select *
from cte
where val in (select s from @t)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by emmim44

quote:

---

Originally posted by nigelrivett

When I run that query I get two rows.
What do you get?
Be nice if you told us what the actual issue is as per my first post
This gives two rows as expected on my machine
note: I've change the value in @finsoftKeys to nvarchar

declare @finsoftKeys nvarchar(1000)
select @finsoftKeys = N'Soför/ Destek|Genel Müdürlük|Soför|Soför,Uygulama Gelistirme-Kredi Kartlari ve ATM Bölümü|Genel Müdürlük|Müdür|Yazilim Gelistirme Müdürü'

declare @t table (s nvarchar(1000))
insert @t select N'Soför/ Destek|Genel Müdürlük|Soför|Soför'
insert @t select N'Uygulama Gelistirme-Kredi Kartlari ve ATM Bölümü|Genel Müdürlük|Müdür|Yazilim Gelistirme Müdürü'


;with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@finsoftKeys+',')
union all
select i=j+1, j=charindex(',',@finsoftKeys+',',j+1) from csvtbl
where charindex(',',@finsoftKeys+',',j+1) <> 0
)
,cte as
(
select val = substring(@finsoftKeys,i,j-i)
from csvtbl
)
select *
from cte
where val in (select s from @t)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

I have tried this :
declare @RMKeys nvarchar(3000)
select @RMKeys = N'Aktif Pasif Riski Yönetimi Bölümü|Genel Müdürlük|Yetkili|Aktif Pasif Riski Yönetimi Yetkilisi,Bilgi Yönetimi - Pazarlama ve Satis Bölümü|Genel Müdürlük|Bilgi Mühendisi|Bilgi Yönetimi Bilgi Mühendisi'

And I got zero record for above...Some of the letters are shown as English character in this forum after submitting. so that is why you dont see them in this post.
You can use the 1st paragph as refence on this article for not showing letters:
http://www.hurriyet.com.tr/gundem/20927487.asp

---