CODEWITHSUNDEEP
coldfusionpadding
epipko
epipko

Reputation: 517

ColdFusion 9: how to pad list values with spaces

In the database, style_id columns is defined as CHAR(14). I need to be able to pad values in the list to 14 with spaces. How do I go about it? Thanks,

<cfset style_list = replace(#FORM.style_id#," ","","all")>

select *
from T
where 
    <cfif IsDefined("form.style_id") and form.style_id is not "">
        style_id in 
                    (
                        <cfqueryparam
                            value="#UCASE(style_list)#"
                            cfsqltype="cf_sql_varchar" 
                            list="yes" />
                    ) 
 </cfif>

Upvotes: 0

Views: 449

Answers (3)

SOS
SOS

Reputation: 6550

Why do you think you need to pad the values? It should work fine without any padding, using cfsqltype CF_SQL_CHAR. Perhaps it's not working as expected because the left side of the comparison (column) is type CHAR while the right (cfqueryparam) is type VARCHAR?

I tested your query and it worked fine with CF 9,0,2,282541 and SQL Server. It also worked correctly with CF 9.0.2 and Oracle 12G. CFQueryparam seemed to handle things automatically.

DDL:

CREATE TABLE someTable(style_id char(14))
INSERT INTO someTable VALUES ('abc'),('efg          ')

CF:

<!--- worked with both CF_SQL_VARCHAR and CF_SQL_CHAR ---> 
<cfset style_list = "abc,efg">
<cfquery name="qTest" datasource="YourDSN">
    SELECT *
    FROM   SomeTable
    WHERE  style_id in 
                    (   <cfqueryparam
                            value="#UCASE(style_list)#"
                            cfsqltype="cf_sql_char" 
                            list="yes" />
                    ) 
</cfquery>
<cfdump var="#qTest#">

Results:

RESULTSET   
query
       STYLE_ID
1      abc  
2      efg

Upvotes: 1

Rain
Rain

Reputation: 331

Use ljustify or rjustify to pad a string with spaces.

You could pre-process the list with a loop:

<cfset padded_list=""/>
<cfloop list="#style_list#" item="style">
  <cfset padded_list=listappend(padded_list, rjustify(style, 14))/>
</cfloop>

If you're using a more recent version of ColdFusion, or a shim, you may also have listmap, which is less ugly:

<cfset style_list=listmap(style_list, function(style) { return rjustify(style, 14); })/>

Upvotes: 1

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Do the opposite. Trim the contents of of the style_id column in your SQL statement so that you can batter match the contents of style_list.

Upvotes: 1

Related Questions