Forum Post: Search for trailing space in character field - odd behaviour?

[CABI]

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

Continue reading...