[RESOLVED] quick query question

[wengang]

Is there a quick way to return all records for a certain field contains any character that is NOT alphanumeric?

I'm changing the rules on a field in my table, and I could pull all the records and run them through code to clean them up, but then I was wondering if there were a quick way to find just those records that need changing.

select * from Table1 where [any character in Column1] not in/like 'ABC..abc..123'

I'm guessing not, unless maybe with Regular Expressions.

This is for Access by the way.
Thanks.

[wild_bill]

SQL Server 2008

declare @temp table(data char(1))

INSERT @temp (data) values
('a'),('@'),('1'),('.')

select *
from @temp
where data not like '[A-Z0-9]'

[wengang]

Sorry , it isn't perfectly clear to me.
For one thing, this is an Access database. So does that expression work? It looks like a regular expression, but I haven't tried that in Access.

But the other thing, it looks like your data is all char(1)
my data is a string up to 255 characters and I want to find any that contains a non-alphanumeric anywhere in the string

[wild_bill]

It looks like Access does support the like operator. http://office.microsoft.com/en-us/ac...001032253.aspx
Since your data contains more than one character, try using the wild card:

where data not like '*[A-Z0-9]*'

or

where data like '*[!A-Z0-9]*'

[wengang]

First one doesn't work, returns no records

Second one was exactly right. Thanks. Final query

where data like '*[!A-Za-z0-9]*'

That cuts 25000 records down to 179.
Thanks a lot.