Finding specific instance in a list when the list starts with a comma

I'm uploading a spreadsheet and mapping the spreadsheet column headings to those in my database. The email column is the only one that is required. In StringB below, the ,,, simply indicates that a column was skipped/ignored.

The meat of my question is this:

I have a string of text (StringA) comes from a spreadsheet that I need to find in another string of text (StringB) which matches my database (this is not the real values, just made it simple to illustrate my problem so hopefully this is clear).

StringA: YR,MNTH,ANNIVERSARIES,FIRSTNAME,LASTNAME,EMAIL,NOTES
StringB: ,YEAR,,MONTH,LastName,Email,Comments <-- this list is dynamic

MNTH and MONTH are intentionally different;

excelColumnList = 'YR,MNTH,ANNIV,FIRST NAME,LAST NAME,EMAIL,NOTES';  
mappedColumnList=  ',YEAR,,MONTH,,First Name,Last Name,Email,COMMENTS';  
mappedColumn= 'Last Name';    

local.index = ListFindNoCase(mappedColumnList, mappedColumn,',', true);

local.returnValue = "";

if ( local.index > 0 )

    local.returnValue = ListGetAt(excelColumnList, local.index);

writedump(local.returnValue);   // dumps "EMAIL" which is wrong

The problem I'm having is the index returned when StringB starts with a , returns the wrong index value which affects the mapping later. If StringB starts with a word, the process works perfectly. Is there a better way to to get the index when StringB starts with a ,?

I also tried using listtoarray and then arraytolist to clean it up but the index is still off and I cannot reliably just add +1 to the index to identify the correct item in the list.

On the other hand, I was considering this mappedColumnList = right(mappedColumnList,len(mappedColumnList)-1) to remove the leading , which still throws my index values off BUT I could account for that by adding 1 to the index and this appears to be reliably at first glance. Just concerned this is a sort of hack.

Any advice?

https://cfdocs.org/listfindnocase

Here is a cfgist: https://trycf.com/gist/4b087b40ae4cb4499c2b0ddf0727541b/lucee5?theme=monokai

UPDATED I accepted the answer using EDIT #1. I also added a comment here: Finding specific instance in a list when the list starts with a comma

Solution

Identify and strip the "," off the list if it is the first character.

EDIT: Changed to a while loop to identify multiple leading ","s.

Try:

while(left(mappedColumnList,1) == ",") {
    mappedColumnList = right( mappedColumnList,(len(mappedColumnList)-1) ) ;
}

https://trycf.com/gist/64287c72d5f54e1da294cc2c10b5ad86/acf2016?theme=monokai

EDIT 2: Or even better, if you don't mind dropping back into Java (and a little Regex), you can skip the loop completely. Super efficient.

mappedColumnList =  mappedColumnList.replaceall("^(,*)","") ;

And then drop the while loop completely.

https://trycf.com/gist/346a005cdb72b844a83ca21eacb85035/acf2016?theme=monokai

<cfscript>
excelColumnList = 'YR,MNTH,ANNIV,FIRST NAME,LAST NAME,EMAIL,NOTES';
mappedColumnList=  ',,,YEAR,MONTH,,First Name,Last Name,Email,COMMENTS';  
mappedColumn= 'Last Name';    

mappedColumnList =  mappedColumnList.replaceall("^(,*)","") ;

local.index = ListFindNoCase(mappedColumnList, mappedColumn,',', true);

local.returnValue = ListGetAt(excelColumnList,local.index,",",true) ;

writeDump(local.returnValue);
</cfscript>

Explanation of the Regex ^(,*):

^ = Start at the beginning of the string.
() = Capture this group of characters
,* = A literal comma and all consecutive repeats.

So ^(,*) says, start at the beginning of the string and capture all consecutive commas until reaching the next non-matched character. Then the replaceall() just replaces that set of matched characters with an empty string.

EDIT 3: I fixed a typo in my original answer. I was only using one list.

writeOutput(arraytoList(listtoArray(mappedColumnList))) will get rid of your leading commas, but this is because it will drop empty elements before it becomes an array. This throws your indexing off because you have one empty element in your original mappedColumnList string. The later string functions will both read and index that empty element. So, to keep your indexes working like you see to, you'll either need to make sure that your Excel and db columns are always in the same order or you'll have to create some sort of mapping for each of the column names and then perform the ListGetAt() on the string you need to use.