CODEWITHSUNDEEP
sqloracle-databasevarchar2
AdamH
AdamH

Reputation: 25

SQL Query - Greater Than with Text Data Type

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.

Upvotes: 1

Views: 1868

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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'
      )

Upvotes: 2

theoa
theoa

Reputation: 71

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.

Upvotes: 1

Related Questions