By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

How to tell last char is not int

ddtpmyra
100+
P: 333
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!
Aug 6 '12 #1

✓ answered by kadghar

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

Expand|Select|Wrap|Line Numbers
  1. DELETE FROM your_table WHERE your_field LIKE '%[0123456789]'

Share this Question
Share on Google+
4 Replies


kadghar
Expert 100+
P: 1,295
you can use a delete statement with a LIKE and a CHARLIST:

Expand|Select|Wrap|Line Numbers
  1. DELETE FROM your_table WHERE your_field LIKE '%[0123456789]'
Aug 7 '12 #2

kadghar
Expert 100+
P: 1,295
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.
Aug 7 '12 #3

P: 85
well you can use the update query like
Expand|Select|Wrap|Line Numbers
  1. update myTable set MemebersID=(Select Left(MembersID,5) form myTable)
  2.  
it will update all the records, and for some specific record, you can use the where clause
...
Addan
Aug 7 '12 #4

ck9663
Expert 2.5K+
P: 2,878
The post is too generic.

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

Expand|Select|Wrap|Line Numbers
  1. UPDATE YourTable
  2. SET MembersId = LEFT(MembersId,5)
  3.  
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
Aug 7 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.