5 Replies Latest reply: Nov 7, 2012 1:10 AM by sujeet Singh RSS

    Seperate delimited data

    ali ceyli

      Hi everyone,

      In my access database, there is fiield showing employee's identiiy but it ıncludes more than one id with ";"

      such as 100;101

      İn qv side 100 id ' s name Alex but because of delimited data ı can not see the name of 100 ID


      ID                         Name

      100;101               ------

      100                         Alex

      101                          David


      How can I seperate the delimited field in two different rows to see names?

        • Re: Seperate delimited data



          You can use subfield function.


          subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'





          • Re: Seperate delimited data
            sivaraj seeman

            Try by loading in script


            load *, subfield(fieldname,';') as new_field

              • Re: Seperate delimited data
                ali ceyli


                Your solution makes delimited fields into one column I wanna explain in an example


                In my qw

                ID                         Name              

                100                        Alex

                100;101                    --

                102                         David


                After your solution 100;101 fields are seperated but I wanna it in one column so ı can see the name of ID

                  • Re: Seperate delimited data
                    sivaraj seeman



                    LOAD * Inline [

                        new_id, Name

                        100,  Alex

                        101,  David





                    LOAD *, SubField(ID_Name,';')as new_id Inline [

                    ID_Name , Name


                    100;101 ,------

                    100,  Alex

                    101,  David




                    LOAD *, ApplyMap('a',new_id) as name

                    Resident b;

                    drop Table b;


                    In actual you have many ID for that you can replace table a with excel sheet that have id and name

                      • Re: Seperate delimited data
                        sujeet Singh


                        Just try this may be it help you....

                        1. if its sure that Your ID is of fixed lenght '3'

                          Then use this -----



                           =  if(len(ID_Name)=3,ID_Name,subfield(ID_Name,';'))


                        Else if it is not so Then,



                            =if(substringcount(ID_Name,';')=0,ID_Name,subfield(ID_Name, ';'))



                        Else give me sample file.....