Query to find non numeric characters

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I'm trying to find any non numeric characters in a field defined as
text with a length of 20. The values in this field should be 6 characters
with numeric data in the records I'm looking for (e.g. - "123456").

There are some rows that have values such as "123456-", however
my query isn't returning them. Here is the syntax I'm using:

SELECT Trim([other_id]) AS Expr1
FROM ids
WHERE (((Trim([other_id])) Like '[000000-999999]'))
;

Any help would be greatly appreciated.

Thanks,

Jerry
 
How about

Where [Other_ID] LIKE "*[!0-9]*"

IF I recall my wild cards correctly that should find any id that contains a
character that is not in the range zero to nine.
 
Jerry said:
I'm trying to find any non numeric characters in a field defined as
text with a length of 20. The values in this field should be 6 characters
with numeric data in the records I'm looking for (e.g. - "123456").

There are some rows that have values such as "123456-", however
my query isn't returning them. Here is the syntax I'm using:

SELECT Trim([other_id]) AS Expr1
FROM ids
WHERE (((Trim([other_id])) Like '[000000-999999]'))
Click to expand...


The [...] wildcard syntax only matches a single character
that's one of the ones in the brackets.

To find records tha only contain digits:
WHERE NOT ([other_id] Like '*[!0-9]*')

Or, to find the records that contain any non-digit
character:
WHERE [other_id] Like '*[!0-9]*'
 
Back
Top