How to tell last char is not int

[ddtpmyra]

We are working on members id, and we wanted to have a standard naming convention.

For instance:
Members ID with charter at last will be keep the way it is.... while members id with number must update.

Example:

A123B --->keep the way it is.
C456D7 ---> 7 should be deleted.

How can we script it in a query this condition?

Thanks for your help!

[kadghar]

you can use a delete statement with a LIKE and a CHARLIST:

DELETE FROM your_table WHERE your_field LIKE '%[0123456789]'

[kadghar]

Sorry about my last post; I didn't understand what you said.

Instead of using DELETE, use an UPDATE statement; the idea is the same.

[Mudassir]

well you can use the update query like

update myTable set MemebersID=(Select Left(MembersID,5) form myTable)

it will update all the records, and for some specific record, you can use the where clause
...
Addan

[ck9663]

The post is too generic.

Do you have an existing data that needs to be cleanup? Then you have to use an UPDATE statement.

UPDATE YourTable
SET MembersId = LEFT(MembersId,5)

If you are dealing with a data entry application and you want to make sure of the formatting, you need to handle that on the front-end.

On both cases, if you want to make sure that only a predefined format can be stored in that column, you have to put in a domain constraint on that column.


Good Luck!!!


~~ CK