Remove Range of Numbers from a Text String

[crystal123456]

I have a field that I need to remove a certain range of numbers from, however, this field is VARCHAR2 and can contain letters.

My current code is as follows (in the Where clause): field.field_name not between '27000000' and '27999999'

The problem is this is also taking out records that are not in this range, such as 27000.

What code can I place in the where clause to solve this issue?

Thanks for the help!

 

[johnherman]

IS NUMERIC

http://www.geekinterview.com/question_details/34549

TO_NUMBER

http://www.nullskull.com/faq/162/convert-string-to-number-in-oracle-using-tonumber-function.aspx

the internet can be your friend.....



==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[crystal123456]

Hi, thanks for the response.

The problem with those is that they throw an error when it gets to a record that actually has a letter included. I need to restrict the where clause to only apply to records that are numeric

 

[Andrzejek]

That's why johnherman gave you this link to check if your field is numeric.
For the records with letters in them you need some other logic.

Have fun.

---- Andy

 

[ChrisHunt]

How about this?

WHERE LPAD(field.field_name,8,'0') NOT BETWEEN '27000000' AND '27999999'

Note that this will truncate the field to 8 characters, so if field_name is longer than this you should LPAD to that full length:

WHERE LPAD(field.field_name,10,'0') NOT BETWEEN '0027000000' AND '0027999999'

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd