OpenEdge 11.3, Windows 2008R2. I've run a query to select records where a field has a trailing space character. Of the four methods used only the substring method gave a correct count (code below). I'm sure it's something I ought to know about but could someone explain why or point me in the direction of a manual? def var iTotalRecordCount as int no-undo. def var iRecordsWithSpace as int no-undo. def var iRecordsWithNoSpace as int no-undo. def var iCountUsingChar as int no-undo. def var iCountUsingSubstring as int no-undo. def var ctemp as char no-undo. for each ItemType no-lock where ItemType.Code contains "Miscellaneous": assign iTotalRecordCount = iTotalRecordCount + 1. if ItemType.Code eq "Miscellaneous " then assign iRecordsWithSpace = iRecordsWithSpace + 1. if ItemType.Code eq "Miscellaneous" then assign iRecordsWithNoSpace = iRecordsWithNoSpace + 1. assign ctemp = ItemType.Code. if ctemp eq "Miscellaneous " then assign iCountUsingChar = iCountUsingChar + 1. if Substring(ItemType.Code,length(ItemType.Code),1) = ' ' then assign iCountUsingSubstring = iCountUsingSubstring + 1. end. results iTotalRecordCount = 21955 iRecordsWithSpace = 21955 iRecordsWithNoSpace = 21955 iCountUsingChar = 21955 iCountUsingSubstring = 6677
This is because EQ operator ignores trailing blanks. Thus, “abc” is equal to “abc “. However, leading and embedded blanks are treated as characters and “ abc” is not equal to “abc”.
With SUBSTRING, you are looking exactly for trailing space and hence you get the right count.
Please refer to the documentation of EQ operator.
This is because EQ operator ignores trailing blanks. Thus, “abc” is equal to “abc “. However, leading and embedded blanks are treated as characters and “ abc” is not equal to “abc”.
With SUBSTRING, you are looking exactly for trailing space and hence you get the right count.
Please refer to the documentation of EQ operator.