Finding Non-alphanumeric Values

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?
 

Cringer

ProgressTalk.com Moderator
Staff member
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.
 

Stefan

Well-Known Member
Code:
def var calpha as char no-undo initial "0123456789abcdefghijklmnopqrstuvwxyz".

for each customer where trim( customer.code, calpha ) > "" no-lock:
   display customer.code.
end.
 

Stefan

Well-Known Member
If you consider ! non alphanumeric then yes. If you consider ! alphanumeric then add it to the initial value of calpha.
 

TheMadDBA

Active Member
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.
 

Cringer

ProgressTalk.com Moderator
Staff member
I consider it non-alphanumeric. I'm just surprised that works for the mix I gave because of how I understand TRIM() to work.
 

Stefan

Well-Known Member
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.

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
 

Cringer

ProgressTalk.com Moderator
Staff member
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.
 

Stefan

Well-Known Member
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.
 

Cringer

ProgressTalk.com Moderator
Staff member
So my understanding of TRIM is correct, and the above will NOT deal with a situation like "abc!!defg!!!hij" as I posted earlier.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
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.
 

Stefan

Well-Known Member
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.

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' ) > ''
 
Top