• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

How to extract the values from a comma separated list?

Experts,

I have a list of values in my database separated by commas: example; 1, 2, 3, 4

I need query syntax to extract this data so that I can use each value.

I've searched for a while but, can't seem to find a simple example that I can build upon.  

Any suggestion?  Thanks Experts.
0
evibesmusic
Asked:
evibesmusic
2 Solutions
 
answer_dudeCommented:
Here's something that should get you started... this code assumes exactly three data values separated by commas... but it can easily be expanded to infinite/unknown numbers...
...

        sVal1 = ""
        sVal2 = ""
        sVal3 = ""
        For x = 1 To Len(rs.Fields(1))
        
            nTo = InStr(x, rs.Fields(1), ",")
            If nTo = 0 Then nTo = Len(rs.Fields(1))
            If sVal1 = "" Then
                sVal1 = Mid$(rs.Fields(1), x, nTo - x)
            ElseIf sVal2 = "" Then
                sVal2 = Mid$(rs.Fields(1), x, nTo - x)
            ElseIf sVal3 = "" Then
                sVal3 = Mid$(rs.Fields(1), x, nTo - x)
            End If
            
            x = nTo
            If x >= Len(rs.Fields(1)) Then
                Exit For
            End If
                    
        Next x
...

Open in new window

0
 
MaartenHensenCommented:
Or you can do:
$datafromdb;
$data = explode(",", $datafromdb);

// and then you can work with all the values... nomather how many there are...

Open in new window

0
 
SheilsCommented:
Use a query similar to the one in the attached file

dbcomma.mdb
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
evibesmusicAuthor Commented:
@MaartenHensen:

Using your example: I am running the following code.  I am continually getting an 'array' when trying to print the exploded result.  Any thoughts?
$get_prefs = "SELECT * FROM permmed_prefs";
		$prefs_data = mysql_query($get_prefs) or die ("Could not run query: " . $get_prefs . "<br />\n" . mysql_error () );
		while($datafromdb = mysql_fetch_array($prefs_data)){
			echo $data = explode(",", $datafromdb['Department']); 
			echo '<br /><br />';
		}

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
print_r
$get_prefs = "SELECT * FROM permmed_prefs";
$prefs_data = mysql_query($get_prefs) or die ("Could not run query: " . $get_prefs . "<br />\n" . mysql_error () );
while($datafromdb = mysql_fetch_array($prefs_data)){
 $data = explode(",", $datafromdb['Department']); 
 print_r ($data);
 echo '<br /><br />';
}

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
or you can foreach the output from the explosion:
$get_prefs = "SELECT * FROM permmed_prefs";
$prefs_data = mysql_query($get_prefs) or die ("Could not run query: " . $get_prefs . "<br />\n" . mysql_error () );
while($datafromdb = mysql_fetch_array($prefs_data)){
 $data = explode(",", $datafromdb['Department']); 
 foreach ($data as $value){
  echo $value;
  echo '<br />';
 }
 echo '<br /><br />';
}

Open in new window

0
 
evibesmusicAuthor Commented:
@NerdsOfTech & MaartenHensen:

Thank you both for your assistance with this solution.  This was my solution:

$get_prefs = "SELECT * FROM permmed_prefs";
$prefs_data = mysql_query($get_prefs) or die ("Could not run query: " . $get_prefs . "<br />\n" . mysql_error () );
      while($datafromdb = mysql_fetch_array($prefs_data)){
            $data = explode(",", $datafromdb['Departments']);
            foreach ($data as $value){
            echo $value;
            echo '<br />';
            }
      echo '<br /><br />';
      }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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