Query List Question

[alsjka]

Hi I have the following list returned from a query.

select value from stringhistory
where tagname = 'mt_mm2_scaledata'
and value is not null



This is the string returned.

------------------ 03/21/2006 11:10 n 3 x 45.23 g Min 35.9 g Max 63.7 g Diff. 27.8 g

Is there a way to break the string apart by sections such as the datetime part, then the "n 3", "x 45.23 g", "Min 35.9 g", "Max 63.7 g", "Diff. 27.8".

I want to be able to get the string and pull these parts out the only problem is they may not alway be in the same sequence. Then after I have the parts removed I need to strip off the characters and leave just the numbers (excluding the date i need the whole date section). Then I need to insert the individual sections into a database.

I have tried various combinations such as listfindnocase to see the different sections and looping through lists but no go. Could someone help explain what I have to do?

Thank You

 

[imstillatwork]

Find someone that knows regex really really well...

is this correct?

03/21/2006 11:10
n 3 x 45.23 g
Min 35.9 g
Max 63.7 g
Diff. 27.8 g

becuase there is definatly patterns there to look for.

will there ALWAYS be MAX, MIN, DIFF value?

Kevin

http://www.lot-o-nothin.com/cfml/

 

[alsjka]

Thanks I managed to find a solution. See below.


<cfquery name="data" datasource="db2">
select * from stringhistory
where tagname = 'mt_mm2_scaledata'
and datetime > {ts '2006-03-20 07:45:00'}
and datetime < {ts '2006-03-20 15:45:00'}
and value is not null
</cfquery>

<cfset clist = "">

<cfloop query="data">
<cfloop list="#data.value#" DELIMITERS="#chr(10)##chr(13)##chr(103)#" index="column">

<cfif #FindNoCase('/',column)# gt 0>
<cfset clist = ListAppend(clist, "#column#")></cfif>
<cfif #left(column,1)# is "n">
<cfset clist = ListAppend(clist, "#trim(ReReplacenocase(column,"n| |","","all"))#")></cfif>
<cfif #left(column,1)# is "x">
<cfset clist = ListAppend(clist, "#ReReplacenocase(column,"x| |","","all")#")></cfif>
<cfif #FindNoCase('min',column)# gt 0>
<cfset clist = ListAppend(clist, "#ReReplacenocase(column,"min| |","","all")#")></cfif>
<cfif #FindNoCase('max',column)# gt 0>
<cfset clist = ListAppend(clist, "#ReReplacenocase(column,"max| |","","all")#")></cfif>
<cfif #FindNoCase('diff.',column)# gt 0>
<cfset clist = ListAppend(clist, "#ReReplacenocase(column,"diff.| |","","all")#")></cfif>
<cfif #FindNoCase('sum',column)# gt 0>
<cfset clist = ListAppend(clist, "#ReReplacenocase(column,"sum| |","","all")#")></cfif>
</cfloop>

<cfset d_scale = ArrayNew(1)>

<cfif #listlen(clist)# eq 7>

<cfquery datasource="db1">
insert into Model_Magic_Scale_Weight (date, sample, avg_weight, min_range, max_range, diff, sum_avg)
values(
<cfloop from="1" to="#ListLen(clist)#" index="listCount">
<cfset d_scale[listCount] =ListGetAt(clist,listCount,",")>
'#d_scale[listCount]#' <cfif #listcount# lt 7>,</cfif>
</cfloop>
)
</cfquery>

<cfoutput>#listlen(clist)# #clist#</cfoutput><br>

</cfif>


<cfset clist = "">

</cfloop>

 

[alsjka]

This was my final solution if anyone has a need for something like this. I had to sort the list so no matter how the list is combined it will always be the exact way i need to have it. If anyone has anything that would make this better or that i could have done better please let me know.




<cfset clist1 = "">
<cfset sortedMix = "">

<cfloop query="data">
<cfloop list="#data.value#" DELIMITERS="#chr(10)##chr(13)##chr(103)#" index="column">

<cfif #FindNoCase('/',column)# gt 0>
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #left(column,1)# is "n">
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #left(column,1)# is "x">
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #FindNoCase('min',column)# gt 0>
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #FindNoCase('max',column)# gt 0>
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #FindNoCase('diff.',column)# gt 0>
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>
<cfif #FindNoCase('sum',column)# gt 0>
<cfset clist1 = ListAppend(clist1, "#column#")>
</cfif>

<cfset sorted = listsort("#clist1#", "textnocase", "asc")>

</cfloop>

<cfoutput>#sorted#</cfoutput><br>

<cfset d_scale = ArrayNew(1)>

<cfif #listlen(clist1)# eq 7>

<cfquery datasource="mp2">
insert into Model_Magic_Scale_Weight (date, diff, max_range, min_range, sample, sum_avg, avg_weight)
values(
<cfloop from="1" to="#ListLen(sorted)#" index="listCount">
<cfset d_scale[listCount] =ListGetAt(sorted,listCount,",")>
'#trim(ReReplacenocase(d_scale[listCount],"min|max|n|g|x|diff.|sum","","all"))#' <cfif #listcount# lt 7>,</cfif>
</cfloop>
)
</cfquery>

</cfif>

<cfset clist1 = "">

</cfloop>