Remove Range of Numbers from a Text String

[crystal123456]

Hello,

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!

 

[gmmastros]

I have a couple of comments about this.

1. There is no such thing as a varchar2 in sql server. I'm guessing that you are actually using Oracle (or another database type). As such, you are likely to get better advice on a forum that is specific to your database engine.

2. If you want to treat data as an integer, then it should be stored in an integer field. This will solve several problems for you. Specifically, you'll use less storage space, sorting will work the way you expect it to, and filters become ridiculously easy.

3. If you cannot change the data type of the column (been there, done that), then you could convert the data to an integer before doing the comparison. Something like this:

Convert(Int, field.field_name) not between 27000000 and 27999999



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[crystal123456]

Hi gmmastros,

My apologies, I am in fact using Oracle SQL.

Letters still need to be in some of the records of the field, so it does need to remain a text field.

I will try the convert function that you advised.

Thanks

 

[SQLScholar]

The convert function will fail unless you remove the letters first. I would suggest a post on the oracle forum will get someone who can help you with the PL\SQL for that.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------