Splitting comma delimited field into two fields

[rookie9]

Hi All:

This question might appear simple, but still I need help :-] please...
I have this table, lets call it table1 with one of the fields as Name composed of LastName, FirstName. I would like to split this field into two, and write a report with two fields, FirstName and LastName.

Thanx a bunch
JN

 

[csteinhilber]

I guess it all depends on what you're doing with the final resultset.

If you're just outputting it, you could use GetToken(), or I'd probably just use ListFirst() and ListRest(). Either way, something like:

<table>
<tr><td>Last Name</td><td>First Name</td><td>...</td></tr>

<CFOUTPUT query=&quot;qryMyQuery&quot;>
<tr><td>#ListFirst(qryMyQuery.name)#</td><td>#ListRest(qryMyQuery.name)#</td><td>...</td></tr>
</CFOUTPUT>
</table>

Or, if you need to do something more involved, and you're using a decent database, you can do the separation directly in the SQL statement you're using to pull your data:

<CFQUERY name=&quot;qryMyQuery&quot; ...>
  SELECT * ,SUBSTR(Table1.name,INSTR(Table1.name,&quot;,&quot;)) AS lastname, SUBSTR(Table1.name,-INSTR(Table1.name,&quot;,&quot;)) AS firstname
    FROM Table1
</CFQUERY>

Or something like that (again, depending very much on what database you're using and what extensions to SQL it recognizes).


-Carl