SQL Query - Greater Than with Text Data Type

Question

I've searched around and couldn't find an answer anywhere.

I'm querying a database that has stored numbers as a VARCHAR2 data type.
I'm trying to find numbers that are greater than 1450000 (where BI_SO_NBR > '1450000'), but this doesn't bring back the results I'm expecting.

I'm assuming it's because the value is stored as text and I don't know any way to get around it.

Is there some way to convert the field to a number in my query or some other trick that would work?
Hopefully this makes sense.

I'm fairly new to SQL.

Thanks in advance.

Which dbms is this related to? (VARCHAR2 is product specific...) — jarlh, Apr 17 '15 at 12:32
These conversions are usually DB specific. You need to convert the text to a number and compare that. — Gábor Bakos, Apr 17 '15 at 12:37
@TimSchmelter, I guess it is, but who knows, maybe some other product has it too? — jarlh, Apr 17 '15 at 12:38
Sorry for any confusion. I believe this is an Oracle database. The server properties show Oracle 9i/10g/11g as the type. I use Aqua Data Studios to query multiple databases and just assumed it was a SQL query. All of the queries I've written (basic stuff) have worked on different types of databases. — AdamH, Apr 17 '15 at 12:53

score 2 · Answer 1 · answered Apr 17 '15 at 12:41

2

If the number is too long to be converted correctly to a number, and it is always an integer with no left padding of zeroes, then you can also do:

where length(BI_SO_NBR) > length('1450000') or
      (length(BI_SO_NBR) = length('1450000') and
       BI_SO_NBR > '1450000'
      )

answered Apr 17 '15 at 12:41

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon. This appears to be working. I can't get the to_number function to work so I'll use this. Thank you all for your help. – AdamH Apr 17 '15 at 12:58

Rahul Tripathi · Accepted Answer · 2015-04-17T13:56:21.017

1

You can try to use like this:

where to_number(BI_SO_NBR) > 1450000

Assuming you are using Oracle database. Also check To_Number function

EDIT:-

You can try this(after OP commented that it worked):

where COALESCE(TO_NUMBER(REGEXP_SUBSTR(BI_SO_NBR, '^\d+(\.\d+)?')), 0) > 1450000

edited Apr 17 '15 at 13:56

answered Apr 17 '15 at 12:35

Rahul Tripathi

168,305
31
280
331

1

Shouldn't be: where to_number(BI_SO_NBR) > 1450000? – ericpap Apr 17 '15 at 12:36
Thanks for your suggestions. I just checked the server properties and it says this is an Oracle 9i/10g/11g database. I use Aqua Data Studio to query the database. I'm unclear on the difference between Oracle and SQL querying? I query both type of databases using Aqua Data and my queries seem to work on either type. – AdamH Apr 17 '15 at 12:39
@AdamH:- Whats the query you are trying? The above query will work in Oracle perfectly – Rahul Tripathi Apr 17 '15 at 12:41
I'm getting an "invalid number" error using the to_number function as mentioned. All of the values in this field should definitely be numbers (no characters). I'll try using the length function like Gordon Linoff mentioned below to see if that will help. It won't let me post the full query in a comment. It says it is too long. – AdamH Apr 17 '15 at 12:48
1

@AdamH:- Try this: `where COALESCE(TO_NUMBER(REGEXP_SUBSTR(BI_SO_NBR, '^\d+(\.\d+)?')), 0) > 1450000` – Rahul Tripathi Apr 17 '15 at 12:55
@RahulTripathi: That appears to have worked. I finally figured out why the to_number function fails by itself. There is one entry in the BI_SO_NBR column that is not a number (it's actually a "-"). Can you explain how that function works? I looked up COALESCE and didn't quite understand what it is doing. It looks like you're telling it to ignore those values, but you don't have the one that we need to ignore ("-") that I can see. It works, but I'm not sure how. – AdamH Apr 17 '15 at 13:51
@AdamH:- I didnt get your question? Do you want me to explain COALESCE? – Rahul Tripathi Apr 17 '15 at 13:55
@RahulTripathi: Yes, I'm not sure I understand how it and the REGEXP_SUBSTR functions are working. Is it replacing any result containing the characters ^\d+(\.\d+)? with a zero? Sorry, it wouldn't let me open chat to avoid adding so many comments. – AdamH Apr 17 '15 at 14:00
@AdamH: `REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern.` So in this case the regex is `^\d+(\.\d+)?`. Now to check what the regex is check [this](https://regex101.com/r/gI8cH4/1). BTW your understanding is correct! – Rahul Tripathi Apr 17 '15 at 14:07

score 1 · Answer 3 · edited Apr 17 '15 at 12:40

If you are talking about Oracle, then:

where to_number(bi_so_nbr) > 1450000

However, there are 2 issues with this:
1. if there is any value in bi_so_nbr that cannot be converted to a number, this can result in an error
2. the query will not use an index on bi_so_nbr, if there is one. You could solve this by creating a function based index, but converting the varchar2 to number would be a better solution.

SQL Query - Greater Than with Text Data Type

3 Answers3