Hi all,

I am Zubair, new to this group. I have one doubt in mysql query. hope anybody will give the right solution.

i want to split some values from one field and want to check in other table. hope you are not clear.

For example,

TABLE1
======
id primarySkill

1 : 2318, 5236, 8541
2 : 5236,8762
3 : 8541, 2318, 5236, 8762

TABLE2
======

id value

2318 java
5236 php
8541 .net
8762 Photoshop

i want TABLE1 ouput like this:
=====================

id primarySkill
1 : java,php, .net
2 : php, Photoshop
3 : .net, java,php, Photoshop

how to write query for this? your swift action shall be highly appreciated

Thanks in advance

- Gnaniyar Zubair

the first thing you will want to do is properly normalize the table

do a search for first normal form

Agreed!

And surely you can only have one primary skill?

try this
declare @TABLE1 table(id int,primarySkill varchar(64))
insert into @table1 select
1 , '2318, 5236, 8541' union all select
2 , '5236,8762' union all select
3 , '8541, 2318, 5236, 8762'

declare @TABLE2 table(id int,value varchar(64))

insert into @table2 select
2318, 'java' union all select
5236, 'php' union all select
8541 ,'.net' union all select
8762, 'Photoshop'

select t.id,s.value into #temp
from
(SELECT
s.id,replace(SUBSTRING(s.primarySkill,charindex(', ',s.primarySkill,v.number),abs(charindex(',',s.pri marySkill,charindex(',',s.primarySkill,v.number)+1 )-charindex(',',s.primarySkill,v.number))),',',' ')as value
FROM @TABLE1 AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
and v.number > 0
and v.number <= len(s.primarySkill)
WHERE substring(',' + s.primarySkill, v.number, 1) = ','
)t
inner join @table2 s on s.id = ltrim(t.value)

select distinct id,stuff((select ','+ value from #temp where id = t.id for xml path('')),1,1,'') from #temp t

but it is not suggested as having primary skill as georgev,r937 said
normalize the table k