final blank space

[Feudalism]

Im working in an automatic language translator. In brief, the application uses
the Replace function to replace certain words in portuguese to their equal in
spanish. Those words are obtained from a table, wich fields are as shown:
portuguese (varchar 50) , spanish (varchar 50).

The application works perfect, but the problem is that some words must only be
replaced if they are found with a blank endspace. Eg. the chain " ? " must be
replaced with " a " . Watch carefully .... the chain is not "?" , but " ? ".
I can save the word " ?" in the database, but I could not find a way to store "
? ".

?Is there a way to do that?

Thanks in advance, Im certain that CF will be the future's language, because
of the great support that we receive.

[jdeline]

I'm not sure what the issue is. The code below adds the string "abcd " (space
at the end) to the database, then successfully reads it back. What am I
missing?

<CFSET chain = "abcd ">

<CFQUERY NAME="test" DATASOURCE="cflogs">
INSERT INTO test (logDate) VALUES('#chain#')
</CFQUERY>

<!--- this select statement returns one record, the one that was just inserted
--->
<CFQUERY NAME="test" DATASOURCE="cflogs">
SELECT * FROM test WHERE logDate = 'abcd '
</CFQUERY>

[Feudalism]

Ok, that works perfect to select the records that end with a blankspace, like
" abcd " or " myself ". The fact is that I cant INSERT a record from with an
ending space. And, the problem is that that record insertion must depend on a
form field....

[mxstu]

So what you are saying is that you can insert the value successfully using your
database IDE, but if you perform the same insert in your CFM page, using form
fields, it trims the last space?

And that if you output the length of the form field before your insert it
displays a length of (3)

BEFORE INSERT: <CFOUTPUT>#Len(form.yourField)#</CFOUTPUT>

... but when you run another query to get the new value it returns a length of
(2)?

AFTER INSERT: <CFOUTPUT>#Len(queryGetNewValue.theValueColumn)# </CFOUTPUT>

[Feudalism]

I cant even insert using the database IDE !

:S

[mxstu]

.... " Ok, that works perfect to select the records that end with a blankspace,
like " abcd " or " myself ". The fact is that I cant INSERT a record from with
an ending space"

Now I'm confused ;-) This sounds like your database DOES contain some values
with spaces at the end. If you cannot insert values with a space at the
end...through CF or through your database IDE, how did those values get into
the database?

[Feudalism]

Actually there is 0 registers that contains end spaces strings.

That's because I cant find a way to insert them there! :(

Using Word, for example, you can "search and replace" the string " ? " for the
string " a ". It's a simple process, but I cant make it run in a CF environment
with dynamically fed words, because I cant insert those strings in the database.

In brief:
I CAN use a replace function with a blankspace ended string.
I CAN NOT insert an blankspace ended string in a database.

Thankx

[mxstu]

If you are not trimming the values in your code, then I would suspect that
maybe a database setting is trimming the trailing spaces... except that you
said it DOES maintain the space before the "?". Only the last space that is
removed. Usually, a database setting that trims values will remove both
spaces.

1) What database are you using?
2) Are you sure you are not trimming the values in your code?
3) I would create a test table and run the following code, to pin-point where
the problem is...

Let me know what results you get when you run the test form



<!--- Step 1: Create a test table --->
CREATE TABLE testCharacterLength(
id int identity(1,1),
testCharacter varchar(50),
testCharacterLen int
)


<!--- Step 2: Save this code as a CFM page and submit the form --->
<cfoutput>
<form action="#CGI.SCRIPT_NAME#" method="post">
<input type="text" name="testCharacter" value=" ? ">
<input type="submit">
</form>
</cfoutput>

<cfif IsDefined("form.testCharacter")>
<cfquery name="testInsert" datasource="#yourDSN#">
SET ANSI_PADDING OFF
INSERT INTO testCharacterLength (testCharacter, testCharacterLen)
VALUES ('#form.testCharacter#', #Len(form.testCharacter)#)
</cfquery>
<cfquery name="getTestValue" datasource="#yourDSN#">
SELECT TOP 1 testCharacter, testCharacterLen
FROM testCharacterLength
ORDER BY ID DESC
</cfquery>
<cfoutput>
Form variable = [#form.testCharacter#]<br>
Form variable len = #Len(form.testCharacter)#<br>
<cfdump var="#getTestValue#">
</cfoutput>
</cfif>

<!--- Step 3: Review the values --->

[Feudalism]

Im using a mySQL database, and Im not trimming in the code.

Ill test your code ASAP. Thanx.

[mxstu]

You may need to change the syntax. The code was written for SQL Server. I do not think mySQL supports
- SET ANSI_PADDING OFF (remove this line)
- IDENTITY (change to the mySQL equivalent)

[mxstu]

Well, it seems like this trimming is by design for some versions of mySQL.
Apparently it just trims the spaces on the end and not the front. If you are
using an early version, you may need to use another datatype if you need to
preserve the spaces.

From documentation:

"VARCHAR values are not padded when they are stored. Handling of trailing
spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained
when values are stored and retrieved, in conformance with standard SQL. Before
MySQL 5.0.3, trailing spaces are removed from values when they are stored into
a VARCHAR column; this means the spaces also are absent from retrieved values.
.....

Before MySQL 5.0.3, if you need a data type for which trailing spaces are not
removed, consider using a BLOB or TEXT type. Also, if you want to store binary
values such as results from an encryption or compression function that might
contain arbitrary byte values, use a BLOB column rather than a CHAR or VARCHAR
column, to avoid potential problems with trailing space removal that would
change data values."

[url]http://dev.mysql.com/doc/mysql/en/char.html[/url]