This is a reproduction of the blog post on ClarionEdge from 2009
This topic came up at work a while ago when trying to perform database operations on strings containing a trailing space.
The Problem: One of our stored procedures was using CHARINDEX to search for strings within the database. The problem appeared when a user defined a search parameter containing a trailing space and the results of the search were not as expected.
SQL and Trailing Blanks
(From the section “Comparison operators, listed” in SQL help)
Trailing blanks are ignored in comparisons; for example, these are equivalent:
WHERE LastName = 'White'
WHERE LastName = 'White '
WHERE LastName = 'White' + SPACE(1)
This is an interesting article: INF: How SQL Server Compares Strings with Trailing Spaces
Sort of related but actually about how data is stored is the ANSI_PADDING setting:
http://technet.microsoft.com/en-us/library/ms187403.aspx
Display Trailing Space in Clarion
The Clarion entry control and text control don’t appear to be able to show trailing spaces from data in a database. You can enter trailing spaces in an entry or text control and save the data but next time you view or edit the form those trailing spaces are not shown.
Funnily enough, if the data does have trailing spaces and the field is not edited then the trailing spaces remain intact when the form is saved.
Fun with SELECT Statements
Have a play with the following SQL to see some examples:
Note the use of “LIKE” rather than “=” and the “[^ ]” will force the WHERE to only match items without the trailing space.
Also, try setting the ANSI_PADDING to “OFF” and see the difference in the results.
SET ANSI_PADDING ON;
CREATE TABLE t1 (charcol CHAR(16) NULL, varcharcol VARCHAR(16) NULL)
INSERT INTO t1 VALUES ('No blanks', 'No blanks');
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ');
SELECT * FROM t1 WHERE varcharcol LIKE 'No blanks '
SELECT * FROM t1 WHERE charcol LIKE 'No blanks '
SELECT * FROM t1 WHERE varcharcol LIKE 'Trailing blank[^ ]'
SELECT * FROM t1 WHERE charcol LIKE 'Trailing blank[^ ]'
DROP TABLE t1
So there are a few thoughts and discoveries that came up in the course of investigating this issue. Of course once you know the reasons there are ways to workaround the problems but I will leave that up to your individual scenario…