Remove empty ValueList strings

 select catword
 from category

<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?
Who is Participating?
_agx_Connect With a Mentor Commented:
Lol, we'll have to save everyone's silly and embarrassing stories for facebook and twitter ;)

Anyway, you're right that the array trick won't work completely. It gets rid of empty strings "", but not blank spaces ie ascii=32:

      ie cList     = abc,,efg,,(space),hi,jk,lmn
         fixedList = abc,efg,(space),hi,jk,lmn         <=== still has empty elements

What you really need is a clever regex. I'm terrible at those beyond the basics. The best I can come up with is something like the code below.  

For example, take a string which has both blank spaces and empty strings all over the place. The regex removes all the spaces before/between/after the commas. Then you can use the array trick on what's left.  The result is a string with no empty elements:

        Full code:
        <!--- sample list --->
        <cfset cList = "   ,abc,,efg,,  ,,xyz">
        <!--- get rid of commas with leading or trailing spaces --->
        <cfset finalList = reReplace(cList, "(\s*,|,\s*)", ",", "all")>
        <!--- get rid of empty elements --->
        <cfset finalList = arrayToList(listToArray(finalList))>

        cList     =    ,abc,,efg,,  ,,xyz
        finalList = abc,efg,xyz

Btw - a real regex guru could probably do it in a single line, lol. But that kind of stuff goes way beyond my rudimentary regex skills.
If you don't want empty values in the first place, the "right" way is to simply exclude them from the query results:

                    SELECT  CatWord
                    FROM    Category
                    WHERE  CatWord IS NOT NULL
                    AND       Len(LTRIM(RTRIM(CatWord))) > 0

... 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(valueList(CatInfo.catword)))>
QsorbAuthor Commented:
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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#">
Be back later.  I've gotta go put out a fire ...
QsorbAuthor Commented:
My fault. I grabbed the wrong Valuelist for the example.I led you astray. Sloppy. It should be ValueList(cKeywords.catword. Now that has the comma delimited list. I'm happy you're always right, means I go to the correct place.

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.
QsorbAuthor Commented:
That's helpful, good ideas, experiment. If all fails, I'll need to return to the original cfquery insert. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.