Remove empty ValueList strings

Posted on 2012-08-21
Last Modified: 2012-08-22
 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?
Question by:Qsorb
    LVL 51

    Expert Comment

    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)))>

    Author Comment

    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.
    LVL 51

    Expert Comment

    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.
    LVL 51

    Expert Comment

    > 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#">
    LVL 51

    Expert Comment

    Be back later.  I've gotta go put out a fire ...

    Author Comment

    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.
    LVL 51

    Accepted Solution

    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.

    Author Closing Comment

    That's helpful, good ideas, experiment. If all fails, I'll need to return to the original cfquery insert. Thanks.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now