|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 23rd, 2004, 03:27 PM
|
|||
|
|||
|
Help with delete statement (all non letter/number entries)
I need to delete all rows from a table in which a certain column contains characters other than letters/numbers/ or a period
So ABC. AB12 .124 would be allowed, but ABC^ or ABC/$ would get deleted This is an Oracle 9i database which AFAIK doesn't support regular expressions, and I'm obviously not that good at SQL  Any help would be appreciated.
|
|
#2
November 23rd, 2004, 04:25 PM
|
|||
|
|||
|
Try the translate(field,if, then) function, it turns translates characters by position in
"if" clause into whatever is in the same position in the "then" clause. Code:
delete from mytable
where
to_number(translate(fld1,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ.~`!@#$%^&*()_+=_|\}]{[":;?/>, ',
'000000000000000000000000000111111111111111111111111111111') > 0;
Any of the characters that get a '1' cause the row to be deleted. Test this out first! -- use rollback to undo the delete.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Help with delete statement (all non letter/number entries) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
