Finding Non-alphanumeric Values

davidaichele

davidaichele

New Member
Without the ability to leverage regular expressions in a SQL query within Progress... does anyone know how I can write a where clause that will identify non-alphanumeric values?
 
I'm not sure a where clause will do what you want. You can certainly code around non alphanumerics. Maybe if you explain a little more what you are trying to achieve a solution could be found.
 
Code: 
def var calpha as char no-undo initial "0123456789abcdefghijklmnopqrstuvwxyz".

for each customer where trim( customer.code, calpha ) > "" no-lock:
   display customer.code.
end.
 
If you consider ! non alphanumeric then yes. If you consider ! alphanumeric then add it to the initial value of calpha.
 
Just be aware that no indexes will be used and this will read every record in the table or every record that satisfies any other (valid) where expressions.
 
I consider it non-alphanumeric. I'm just surprised that works for the mix I gave because of how I understand TRIM() to work.
 
Any function (including a non-existent regexp) on the left side of a where clause will result in a table-scan.

And as to how trim works, you need to update your understanding:

trim-chars
A character expression that specifies the characters to trim from expression. If you do not specify trim-chars, the TRIM function removes spaces, tabs, line feeds, and carriage returns.
Click to expand...

We use it for the opposite, to enforce numeric entry in a character field (note the extra replace to prevent a space inside the 'numeric' entry from passing the trim):

Code: 
IF TRIM( REPLACE( TRIM( i_cacc_nr ), " ":U, "#":U),"0123456789":U ) > "" THEN
 
So why doesn't this trim anything then?
Code: 
def var calpha as char no-undo initial "0123456789abcdefghijklmnopqrstuvwxyz".

message trim("!!jghfjdh$%^kfjfdk££",calpha) view-as alert-box.
 
Because you have characters in your string that do not match calpha. TRIM works from the left and right of the string and will stop as soon as a character is found that does not match what should be trimmed.
 
So my understanding of TRIM is correct, and the above will NOT deal with a situation like "abc!!defg!!!hij" as I posted earlier.
 
I thought this question was about a SQL query, not an ABL query, given that the OP mentioned SQL and it's in the SQL-92 forum.
 
Rob Fitzpatrick said:
I thought this question was about a SQL query, not an ABL query, given that the OP mentioned SQL and it's in the SQL-92 forum.
Click to expand...

Good point, the same principle applies - but with a small addition for case-sensitivity of sql:

Code: 
select * from pub.mcomp where rtrim ( lower( main_comp_code ), '0123456789abcdefghijklmnopqrstuvwxyz' ) > ''
 
You must log in or register to reply here.
Back
Top