Remove empty ValueList strings
<CFQUERY NAME="CatInfo" DATASOURCE="NEWS1">
select catword
from category
</CFQUERY>
<cfset clist = ValueList(CatInfo.catword)
CATWORD is a comma delimited list of words. Strings may include one, two, or even three words, each string separated by comma and spaces between each multi-word strings.
I need to eliminate blank or empty words, that is, resulting strings found empty (separated by commas).
I cannot replace the comma like this:
<cfset clist = ReplaceNoCase(clist, ",", "", "All")>
because it removes all commas,
I can't just remove spaces as there are spaces between two-word strings. So, without having to rewrite the cfquery, is there a way to handle this and still use ValueList?
select catword
from category
</CFQUERY>
<cfset clist = ValueList(CatInfo.catword)
CATWORD is a comma delimited list of words. Strings may include one, two, or even three words, each string separated by comma and spaces between each multi-word strings.
I need to eliminate blank or empty words, that is, resulting strings found empty (separated by commas).
I cannot replace the comma like this:
<cfset clist = ReplaceNoCase(clist, ",", "", "All")>
because it removes all commas,
I can't just remove spaces as there are spaces between two-word strings. So, without having to rewrite the cfquery, is there a way to handle this and still use ValueList?
ASKER
But CatWord is a LIST of keywords, all separated by commas. It is the blank, or empty string between commans I want to avoid.
And before you mention it, I also know it would be best not to save the empty strings on the first hand.
So, unless I'm missing something, it's the empty element in the CatWord I want to avoid. If there is no way then I'll return to how I'm saving it and see what I can do. I just need to know if there's a way to solve the problem without going to the source when it was saved.
And before you mention it, I also know it would be best not to save the empty strings on the first hand.
So, unless I'm missing something, it's the empty element in the CatWord I want to avoid. If there is no way then I'll return to how I'm saving it and see what I can do. I just need to know if there's a way to solve the problem without going to the source when it was saved.
Did you try it? It should do exactly what you asked with *empty* strings. If you're talking about *blank* spaces, that's a different kettle of fish. If so, please post a concrete example of the string. Both the "bad" value and what you'd like it to be instead.
> But CatWord is a LIST of keywords,
Wait a minute ... in your last thread you said category.CatWord contained *invidual* words. Otherwise, I'm not sure why you're using valueList. As it's purpose is to create a csv string out of individual values.
Can you just post a concrete example of #clist# . So we can cut to the chase and don't go round and round again .. ;-)
ie <cfset clist = ValueList(CatInfo.catword)
<cfdump var="#clist#">
Wait a minute ... in your last thread you said category.CatWord contained *invidual* words. Otherwise, I'm not sure why you're using valueList. As it's purpose is to create a csv string out of individual values.
Can you just post a concrete example of #clist# . So we can cut to the chase and don't go round and round again .. ;-)
ie <cfset clist = ValueList(CatInfo.catword)
<cfdump var="#clist#">
Be back later. I've gotta go put out a fire ...
ASKER
My fault. I grabbed the wrong Valuelist for the example.I led you astray. Sloppy. It should be ValueList(cKeywords.catwor
Put out the fire but let's not get burned. Can you tell us about it, and while you're at it, how you got started in programming, your first kiss, and how it feels to be on top of the genius list?
I know. This is no place for such questions. Just kidding.
Put out the fire but let's not get burned. Can you tell us about it, and while you're at it, how you got started in programming, your first kiss, and how it feels to be on top of the genius list?
I know. This is no place for such questions. Just kidding.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's helpful, good ideas, experiment. If all fails, I'll need to return to the original cfquery insert. Thanks.
SELECT CatWord
FROM Category
WHERE CatWord IS NOT NULL
AND Len(LTRIM(RTRIM(CatWord)))
... but you said you don't want to do that. So another option is a simple array trick. Convert the list to an array then back to a list. Since CF ignores empty elements, the conversions drop empty elements
<cfset fixedList = arrayToList(listToArray(va