Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Order Paragraph Text Alphabetically
- Thread starter rmz8
- Start date
Okay the first time around I thought you meant to order all words in a paragraph by alphabetical order. Now I'm not sure that's what you meant.
Do you mean you want to be able to include a long variable character field in an ORDER BY clause, like use Memo for Access or whatever applies? Unfortunately, you can't. It's just too much work for a database to do so it draws the line there. If you plan on using this field in that way perhaps you should also include a Text field with the first few words, using that field to alphabetize with.
--------------------------------------
In the rare case that I was actually on the right track the first time I read your post, here is the answer to that!
Your database will see the text field as one item, so it's basically alphabetizing one thing. There are probably some good ways to use your database's functions to organize the data, but why not use ColdFusion?
First you'll have to figure out a way to break apart every word. You could probably do this with spaces that are already there.
Next you might want to get rid of punctuation (REReplace should work with patterns).
Then you can put this space-delimited list into an array (ListToArray). Then sort this array (ArraySort).
It'll be tough because a regular paragraph can contain lots of interesting punctuation. What about quotes before words? What about words at the start of a block of text in parentheses? This is a tough project, but it's doable.
Do you mean you want to be able to include a long variable character field in an ORDER BY clause, like use Memo for Access or whatever applies? Unfortunately, you can't. It's just too much work for a database to do so it draws the line there. If you plan on using this field in that way perhaps you should also include a Text field with the first few words, using that field to alphabetize with.
--------------------------------------
In the rare case that I was actually on the right track the first time I read your post, here is the answer to that!
Your database will see the text field as one item, so it's basically alphabetizing one thing. There are probably some good ways to use your database's functions to organize the data, but why not use ColdFusion?
First you'll have to figure out a way to break apart every word. You could probably do this with spaces that are already there.
Next you might want to get rid of punctuation (REReplace should work with patterns).
Then you can put this space-delimited list into an array (ListToArray). Then sort this array (ArraySort).
It'll be tough because a regular paragraph can contain lots of interesting punctuation. What about quotes before words? What about words at the start of a block of text in parentheses? This is a tough project, but it's doable.
- Thread starter
- #3
I did want to alphabetize a bunch of memo fields. What if I take the first word of each one of the paragraphs and then organize it that way? That would work except that some have HTML, so <font color=red> might be the first part of a paragraph. Clearly that would not be an effective means of organization. Is there anyway to strip the HTML (anything in <> ) from before the first word of the paragraph and then take the first word from the paragraph. I would do this for all of the paragraphs in the query AND then alphabetize them according to the first word.
Ryan ;-]
Ryan ;-]
Are you planning on doing this alphabetization AFTER you get the resultset, using ColdFusion? Or are you planning on doing it using a SQL query, BEFORE ColdFusion? It's more efficient by far to let your database do the work. Unfortunately, you'll need to do the up-front work.
I'm assuming you have some form that is letting you enter the data that is going in the Memo field? Do you enter the HTML tags in this form or are you letting ColdFusion do it (using ParagraphFormat() or something)? If you want the database to do the work of sorting then you should have a completely new Text column that has this first word (though maybe the first few words is better as a lot of sentences start with the same word like "the" or "I"
. Yes, this means the nastiness of going through your data and making these fields for each row.
That's the best way in the long run. If you want the short-term fix (one that will load slower as it is much less efficient) -- you gotta do what you gotta do. You're going to have to do something like this:
1. Pull the fields out of the database.
2. Grab the first few words from each Memo field that are not HTML tags.
3. Resort all of your data based on these words.
4. Output the new order of data.
Step 1 I'm guessing you already have.
Steps 2 through 4 should be worked on extensively to get perfect, but this should get you started (I repeat, this is not optimized but a working model!).
There are probably other great ideas out there for how to do this, so ask around!
I'm assuming you have some form that is letting you enter the data that is going in the Memo field? Do you enter the HTML tags in this form or are you letting ColdFusion do it (using ParagraphFormat() or something)? If you want the database to do the work of sorting then you should have a completely new Text column that has this first word (though maybe the first few words is better as a lot of sentences start with the same word like "the" or "I"
. Yes, this means the nastiness of going through your data and making these fields for each row.That's the best way in the long run. If you want the short-term fix (one that will load slower as it is much less efficient) -- you gotta do what you gotta do. You're going to have to do something like this:
1. Pull the fields out of the database.
2. Grab the first few words from each Memo field that are not HTML tags.
3. Resort all of your data based on these words.
4. Output the new order of data.
Step 1 I'm guessing you already have.
Steps 2 through 4 should be worked on extensively to get perfect, but this should get you started (I repeat, this is not optimized but a working model!).
Code:
<cfquery name="get" datasource="dsn">
SELECT memofield FROM mytable
</cfquery>
<cfset aMemo = ArrayNew(2)>
<cfloop query="get">
<cfset newStr = REReplaceNoCase(get.memofield,"<.>","","ALL")>
<cfset newStr = Left(VARIABLES.newStr,30)>
<!--- put the first few words in the first element --->
<cfset aMemo[get.CurrentRow][1] = VARIABLES.newStr>
<!--- put the whole memofield in the second element --->
<cfset aMemo[get.CurrentRow][2] = get.memofield>
</cfloop>
<cfset aMemoSort = ArrayNew(2)>
<cfset aMemoSort = ArraySort(aMemo, "textnocase")>
<cfoutput>
<cfloop from="1" to=#ArrayLen(aMemoSort)# index="i">
<p>#aMemoSort[i][2]#</p>
</cfloop>
</cfoutput>- Thread starter
- #5
- Status