Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Post Unanswered: Split Values by comma and Check in another Table?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first thing you will want to do is properly normalize the table

    do a search for first normal form

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Agreed!

    And surely you can only have one primary skill?
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •