[Last Call] Learn how to a build a cloud-first strategyRegister Now


Remove empty ValueList strings

Posted on 2012-08-21
Medium Priority
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
  • 5
  • 3
LVL 52

Expert Comment

ID: 38318437
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

ID: 38318628
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 52

Expert Comment

ID: 38318703
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

LVL 52

Expert Comment

ID: 38318710
> 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 52

Expert Comment

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

Author Comment

ID: 38318778
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 52

Accepted Solution

_agx_ earned 2000 total points
ID: 38321082
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

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

831 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