This is a weird one I've never noticed before.
I am running a SELECT query in ColdFusion 8 against an iSeries/DB2 database.
Here's the query in it's simplest form:
<cfquery name="qMyData" datasource="#APPLICATION.DataSource#">
SELECT 'XXX 111'
FROM MYLIB.MYTABLE
</cfquery>
The value being selected is XXX 111 (two spaces between the two strings). The value returned by the query is XXX 111 (it removes the second space):
<cfdump var="#qMyData#" />
returns the following query result:
<TABLE class=cfdump_query>
<TBODY>
<TR>
<TH style="CURSOR: hand" class=query title="click to collapse" onclick=cfdump_toggleTable(this); colSpan=5>query</TH></TR>
<TR bgColor=#eeaaaa>
<TD style="CURSOR: hand" class=query title="click to collapse" onclick=cfdump_toggleRow_qry(this);> </TD>
<TD class=query>00001</TD>
<TR>
<TD style="CURSOR: hand" class=query title="click to collapse" onclick=cfdump_toggleRow_qry(this);>1</TD>
<TD vAlign=top>XXX 111 </TD>
Why is ColdFusion removing the second space? Is there a workaround to ensure that I am getting the EXACT value I'm trying to retrieve?
ColdFusion isn't removing the whitespace, it's the browser, or more directly, HTML.
If you output your values like @Tomalak said, or something like this:
<cfoutput query="qMyData">
#replace(qMyData.myColumn," "," ","all")#
</cfoutput>
You will see that the whitespace is there.
Check this out: http://www-sul.stanford.edu/tools/tutorials/html2.0/whitespace.html
You should replace your whitespace with non-breaking spaces to avoid the collapse.