Stepping through a query result set, replacing one string with another.

[RedOctober57]

I want to write a function that replaces the occurance of a string with another different string.  I need it to be a CF fuction that is callable from another CF function.  I want to "hand" this function an SQL statement (a string) like this:   (Please note, don't bother commenting that "there are eaiser ways to write this SQL..., I've made this simple example to get to the point where I need help.  I have to use a "sub_optimal" SQL syntax just to demonstrate the situation)

Here is the string I want to pass to the function:

SELECT

  [VERYLONGTABLENAME].FIRST_NAME,

  [VERYLONGTABLENAME].LAST_NAME,

  [VERYLONGTABLENAME].ADDRESSS

FROM

  LONGTABLENAME [VERYLONGTABLENAME]

Here is the contents of the ABRV table:

TBL_NM,  ABRV    <!--- Header row--->

VERYLONGTABLENAME, VLTN

SOMEWHATLONGTALBENAME, SLTN

MYTABLENAME, MTN

ATABLENAME, ATN

The function will return the original string, but with the abreviations in place of the long table names, example:

SELECT

  VLTN.FIRST_NAME,

  VLTN.LAST_NAME,

  VLTN.ADDRESSS

FROM

  LONGTABLENAME VLTN

Notice that only the table names surrounded by brackets and that match a value in the ABRV table have been replaced.  The LONGTABLENAME immediately following the FROM is left as is.

Now, here is my dum amatuer attempt at writing said function:  Please look at the comment lines for where I need help.

      <cffunction name="AbrvTblNms" output="false" access="remote" returntype="string" >
        <cfargument name="txt" type="string" required="true" />

        <cfset var qAbrvs="">  <!--- variable to hold the query results --->

        <cfset var output_str="#txt#">  <!--- I'm creating a local variable so I can manipulate the data handed in by the TXT parameter.  Is this necessary or can I just use the txt parameter? --->

        <cfquery name="qAbrvs" datasource="cfBAA_odbc" result="rsltAbrvs">
            SELECT TBL_NM, ABRV FROM BAA_TBL_ABRV ORDER BY 1
        </cfquery>

     <!--- I'm assuming that at this point the query has run and there are records in the result set --->

    <cfloop index="idx_str" list="#qAbrvs#">      <!--- Is this correct?  I think not. --->
    <cfset output_str = Replace(output_str, "#idx_str#", )  <!--- Is this correct?  I think not. --->

    </cfloop>               <!--- What am I looping on?  What is the index? How do I do the string replacement? --->

        <!--- The chunck below is a parital listing from my Delphi Object Pascal function that does the same thing

               I need to know how to write this part in CF9
      while not Eof do
        begin
          s := StringReplace(s, '[' +FieldByName('TBL_NM').AsString + ']', FieldByName('ABRV').AsString, [rfReplaceAll]);
          Next;
        end;
        --->

    <cfreturn output_txt>

    </cffunction>

I'm mainly struggling with syntax here.  I know what I want to happen, I know how to make it happen in another programming language, just not CF9.  Thanks for any help you can provide.

[ilssac · Accepted solution]

RedOctober57 wrote:

...
Thanks for any help you can provide.

One:

<cfset var output_str="#txt#">  <!--- I'm creating a local
variable so I can manipulate the data handed in by the TXT parameter.
Is this necessary or can I just use the txt parameter? --->

No you do not need to create a local variable that is a copy of the arguments variable as the arguments scope is already local to the function, but you do not properly reference the arguments scope, so you leave yourself open to using a 'txt' variable in another scope.  Thus the better practice would be to reference "arguments.txt" where you need to.

Two:

I know what I want to happen, I know how to make it happen in another programming language, just not CF9.

Then a better start would be to descirbe what you want to happen and give a simple example in the other programming language.  Most of us are muti-lingual and can parse out clear and clean code in just about any syntax.

Three:

<cfloop index="idx_str" list="#qAbrvs#">      <!--- Is this correct?  I think not. --->

I think you want to be looping over your "qAbrvs" record set returned by your earlier query, maybe.

<cfloop query="qAbrvs">

Four:

<cfset output_str = Replace(output_str, "#idx_str#", )  <!--- Is this correct?  I think not. --->

Continuing on that assumption I would guess you want to replace each instance of the long string with the short string form that record set.

<cfset output_str = Replace(output_str,qAbrs.TBLNM,qAbrs.ABRV,"ALL")>

Five:

</cfloop>               <!--- What am I looping on?  What is the index? How do I do the string replacement? --->

If this is true, then you are looping over the record set of tablenames and abreviations that you want to replace in the string.