help with string manipulation (Find,Replace)

[cfdeveloper]

I've tried hard to find a solution to my problem but have had no luck.

I am pulling the data from the 'comments' column in a database table. The data in the comments column looks like this.

"TEST - CLOSE CALL

*** 10/28/2003 2:53:52 ***

THIS IS A TEST- PLEASE CLOSE CALL

*** 04/06/2003 12:33:5"
I want to only display the first comment, in this case "TEST - CLOSE CALL". I am able to do this.
Here is the code I've written to display the first comment.
#Left(qResults.Slug,Find('#Chr(13)#',qResults.Slug))#
The above code searches for a new line in the comment column and displays everything before the new line.

The problem I'm having is looking for the first comment (top-most comment) when the data stored in the column begins with a new line

"




MOVE FOLDER

*** 04/06/2003 13:41:47 ***

blah, blah

*** 04/06/2003 12:33:5"

As shown in the above example, there a number of new line characters before the first comment ("Move Folder").

I don't know how to approach this problem, I want to replace all new line characters before the first occurance of a character between a-z. Can someone please show me how to do this please

Cheers,
cfcoder

 

[bhightower]

try using a LTrim()

#Left(LTrim(qResults.Slug),Find('#Chr(13)#',LTrim(qResults.Slug)))#

------------------------------------------------------------------------------
brannonH
if( !succeed ) try( );

 

[cfdeveloper]

Many Thanks Brannon for your help. Your code is good but there it does handle the comments column properly if the comments column has the following data:
"


*** 09/05/2002 18:46:21 ***
Closing as no point to this log
Hot Swap
*** 17/04/2002 13:08:15 ***
OK can ayone tell me where the Request for the replacement PC is."

I want to display the string "Closing as no point to this log
Hot Swap" whereas if I use your code, the string that's displayed is:
" 09/05/2002 18:46:21 "

Any ideas?!

 

[TruthInSatire]

I haven't tested it but play with this.

<cfset startPos = reFind(alnum,qResults.Slug)>
<cfset endPos = find('#char(13)#',qResults.Slug, startPos) - reFind(alnum,qResults.Slug)>

#mid(qResults.Slug,startPos, endPos)#


Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[cfdeveloper]

what is "alnum"?!

 

[TruthInSatire]

its CF short hand for the regular expression [A-Za-z0-9]
basicaly it finds the first alpha or number char

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[bhightower]

It looks to me that you need to validate your data upon update/insert to a certain format that way you can pull out what you need when you need.....

with all these situations you have presented you are going to need a ton of code just to get back a few words in the comments column.....


mho


------------------------------------------------------------------------------
brannonH
if( !succeed ) try( );

 

[TruthInSatire]

Validating input isn't a bad idea, however, mid only uses 3 lines of code if you break up the start and end points. Depending how many times you need to do this could bog down the processing though.

I messed up the "alnum" syntax. just use

<cfset startPos = reFind("[A-Za-z0-9]",qResults.Slug)>
<cfset endPos = find('#char(13)#',qResults.Slug, startPos) - reFind(alnum,qResults.Slug)>

#mid(qResults.Slug,startPos, endPos)#

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[bhightower]

the situations given are

"TEST - CLOSE CALL

*** 10/28/2003 2:53:52 ***

THIS IS A TEST- PLEASE CLOSE CALL

*** 04/06/2003 12:33:5"

AND

"




MOVE FOLDER

*** 04/06/2003 13:41:47 ***

blah, blah

*** 04/06/2003 12:33:5"

*** 09/05/2002 18:46:21 ***
Closing as no point to this log
Hot Swap
*** 17/04/2002 13:08:15 ***

------------------------------------------------------------------------------
brannonH
if( !succeed ) try( );

 

[TruthInSatire]

You're right, that third one botches things up huh? ok, a little more coding but this should work.

<cfset firstDate = reFind("***", qResults.Slug)>
<cfset firstChar = reFind("[A-Za-z0-9]",qResults.Slug)>
<cfif firstDate gt firstChar>
<cfset startPos = firstChar>
</cfelse>
<cfset endFirstDate = reFind("***", qResults.Slug), firstDate + 1>
<cfset startPos = reFind("[A-Za-z0-9]",qResults.Slug, endFirstDate)>
<cfset endPos = find('#char(13)#',qResults.Slug, startPos) - reFind(alnum,qResults.Slug)>

#mid(qResults.Slug,startPos, endPos)#

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[cfdeveloper]

bombboy you code does not work for the following situation

*** 09/05/2002 18:46:21 ***
Closing as no point to this log
Hot Swap
*** 17/04/2002 13:08:15 ***

Can you think of anything that would handle this situation?!

 

[TruthInSatire]

Did the last one i posted work?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[cfdeveloper]

no I'm getting the follwoing error:
Malformed regular expression "***".


<cfset firstDate = reFind("***", qResults.Slug)>
<cfset firstChar = reFind("[A-Za-z0-9]",qResults.Slug)>
<cfif firstDate gt firstChar>
<cfset startPos = firstChar>
</cfif>
<cfset endFirstDate = reFind("***", qResults.Slug, firstDate + 1)>
<cfset startPos = reFind("[A-Za-z0-9]",qResults.Slug, endFirstDate)>
<cfset endPos = find('#char(13)#',qResults.Slug, startPos) - reFind(alnum,qResults.Slug)>

#mid(qResults.Slug,startPos, endPos)#

 

[cfdeveloper]

ok, I figured out what was causing the error, but the problem described in situation 3 is still there.

<cfset firstDate = reFind("[***]", qResults.Slug)>
<cfset firstChar = reFind("[A-Za-z0-9]",qResults.Slug)>
<cfif firstDate gt firstChar>
<cfset startPos = firstChar>
<cfelse>
<cfset endFirstDate = reFind("[***]", qResults.Slug, firstDate + 1)>
<cfset startPos = reFind("[A-Za-z0-9]",qResults.Slug, endFirstDate)>
</cfif>
<cfset endPos = find('#chr(13)#',qResults.Slug, startPos) - reFind("[A-Za-z0-9]",qResults.Slug)>

#mid(qResults.Slug,startPos, endPos)#

 

[TruthInSatire]

ok I created a string and used "/" vice "#char(13)#" to test it. if you copy and paste this code exactly as I have it you can see the example work. Just change "myString" to your query.field and "/" to "#char(13)#" I haven't tested for the other two conditions, but I think it'll work.

<cfset mystring = "/
*** 09/05/2002 18:46:21 ***/
Closing as no point to this log Hot Swap/
*** 17/04/2002 13:08:15 ***">
<cfset firstDate = reFind("[***]", mystring)>
<cfset firstChar = reFind("[A-Za-z0-9]",mystring)>
<cfif firstDate gt firstChar>
<cfset startPos = firstChar>
<cfelse>
<cfset endFirstDate = reFind("[***]", mystring, firstDate + 3)>
<cfset startPos = reFind("[A-Za-z0-9]",mystring, endFirstDate)>
</cfif>
<cfset endPos = find("/",mystring, startPos+4) - startPos>

<cfoutput>
firstDate: #firstDate#<br>
firstChar: #firstChar#<br>
endFirstDate: #endFirstDate#<br>
startPos: #startPos#<br>
endPos: #endPos#<br>
string: #len(myString)#<br>
#mid(myString,startPos, endPos)# </cfoutput>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[cfdeveloper]

bombboy, you are a brilliant programmer mate! No doubt about that. Your code is a bit complicated, I did just what you asked me to. I was wondering if there was a simple solution to situation 3

 

[TruthInSatire]

bhightower was right

with all these situations you have presented you are going to need a ton of code just to get back a few words in the comments column.....

The best thing to do is maybe change your input format so you're not filtering your data on output. It's easier to seperate on input... if you have records that keep adding, a seperate table using ID's to maintain a one to many relationship would be best. Other than that I think that's about the smallest amount of code you're going to get away with, even if you use a different method.

main table
1 TicketNumber1 details
2 ticketNumber2 detailsOnTicket2

Remarks Table
1 remark1 remarkDate1
1 remark2 remarkdate2
1 remark3 remarkDate3
2 remark1 remarkDate1
2 remark2 remarkdate2

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[cfdeveloper]

Guys, I've took bits of code that you posted and got some help from my colleague and have found a solution to this problem.

<cfif Find('#Chr(13)#',qResults.Slug)>
<cfset tmp=REReplaceNoCase(qResults.Slug,'^[^a-z0-9_]*([a-z0-9_])','\1')>
<cfif Find('***',tmp) and trim(Left(tmp,Find('***',tmp)-1)) neq "">
<cfset str = REReplace(qResults.Slug,"^\s*([*]{3}.*?[*]{3})?\s*","")>
<cfset str = REReplace(str,"^(.*?)\s*[*]{3}.*$","\1")>
#str#
<cfelseif trim(Left(tmp,Find('#Chr(13)#',tmp))) neq "">
#trim(Left(tmp,Find('#Chr(13)#',tmp)))#
<cfelse>
#tmp#
</cfif>
</cfif>

Many thanks for all your help

 

[cfdeveloper]

I need your help with something else. This is not related to my original query, but is similar. When the comment field data is displayed in a text area, it looses the new line character. Don't know why. Here is an example.

MOVE FOLDER *** 04/06/2003 13:41:47 *** blah, blah *** 04/06/2003 2:33:5"

When I want display it like this:

MOVE FOLDER

*** 04/06/2003 13:41:47 ***

blah, blah

*** 04/06/2003 12:33:5"

Can you show me how to do this please.
Best Regards,
cfcoder