Invalid Number when converting the string value into Number

[sudhir pavan]

Hi

I have data as described below.

ward -string column
User_id - Id column

Ward --> User_ID
------------ -------------

9435167847467 --> 1041358
9435167847467 --> 1041359
009999441 --> 1041439
rtrt090909 --> 1002122
rrrrrrrrrrrr --> 1548722

Need to get the data from ward column where the data has only numeric value and then the value should be greater then 9999450.
so i need to get only 1) & 2) row .But it shows me invalid number.

I used the below query
----------------------------

select ward,user_id from (

SELECT
to_number(ward) as ward,
USER_ID
FROM NEW_USER
WHERE (REGEXP_LIKE(WARD,'^[[:digit:]]+$') AND LENGTH(WARD)>=8))
where ward>9999450;

can you let me know where the issue is?

[sudhir pavan]

got the result when applied to_number function to where clause also.

[Jeanne Boyarsky]

sudhir pavan wrote:got the result when applied to_number function to where clause also.

Good. Thanks for posting that you found the answer. It will help future readers with the same question.