query to pull certain format?

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a table with info in a certain format..it looks
like this.

6dig# name,lastname

so it would be

123456 scott,jones

is there a way to create a query that if a column is not
in that format it will bring it back as being wrong?

I then have another column that I need to create a query
that will pull anything that does not include a letter or
number so the following would be ok

123456kkkj but the next would not be ok

1234556-0h

Thanks for any help!
Scott
 
Scott said:
I have a table with info in a certain format..it looks
like this.

6dig# name,lastname

so it would be

123456 scott,jones

is there a way to create a query that if a column is not
in that format it will bring it back as being wrong?

I then have another column that I need to create a query
that will pull anything that does not include a letter or
number so the following would be ok

123456kkkj but the next would not be ok

1234556-0h
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For this:

6dig# name,lastname

to find un-matching columns, you could use a criteria like this:

WHERE ColumnName Not Like "###### *, *"

The cross-hatch symbol means any valid numeric character; in this case 6
numeric characters.

Design Note: This column's values should really be in 3 columns: a
numeric column, a first name column, a last name column.

===

To find records not like this "123456kkkj." If you mean the ColumnName
has to have 6 numbers preceeding 4 lower-case letters, then you may wish
to try this criteria:

WHERE ColumnName Not Like "######[a-z][a-z][a-z][a-z]"

The [a-z] indicates a single character must be one of the lower-case
characters between (inclusively) a and z.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWkWYechKqOuFEgEQKfBgCfUEjHiAZSRzDg6faOUafvJ7Fa6QYAnjAv
nsmArhiYRZo55K2+YyCtEKO3
=ImFB
-----END PGP SIGNATURE-----
 
Back
Top