# Oracle SQL Query to fetch data if a field value has atleast only one or all numerals

One of my friend wanted to write a SQL query which must identify records which have atleast one numeral in a particular varchar field.

To_number function will not be useful to do this. Oracle has a function called IsNumber which identifies whether a field value is numeric or else. Unfortunately this function would not solve the above problem as we are looking for a field with at least one numeral and not fully numeral.

We used translate and length functions to write such a query.

Query:

SELECT *

FROM TABLENAME

WHERE

LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) <> LENGTH(FIELDTOBECHECKED)

How it works?

LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) will give length of the field – FIELDTOBECHECKED accounting only the alphabetical characters and ignores any numerals.

LENGTH(FIELDTOBECHECKED) will give the total length of the string. In case there is at least only one numeral in the string then the left side expression will calculate the length of the string ignoring the numerals in it. As this would be not equal to the right side expression which is the total length of the string data for that row would be fetched.

Below I have explained this with 3 different examples.

Case 1:

In case the value of the FIELDTOBECHECKED is "MyString", the left side function would give length of 8 which is also equal to the actual length of the field 8(right side expression) and hence it does not fetch that row.

Case 2:

In case the value of the FIELDTOBECHECKED is "12071987", the left side function would give length of 0(zero) which is not equal to the actual length of the field 8(right side expression) and hence it does not fetch that row. This difference in length would fetch the results for that particular row as it contains at least all numerals.

If you are only looking for a condition to fetch data which has all numbers in a field then make the WHERE condition as LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) is NULL.

Case 3:

In case the value of the FIELDTOBECHECKED is "My 1st String", the left side function would give length of 12 which is not equal to the actual length of the field 13(right side expression) and hence it does not fetch that row. Spaces are also considered in the length since '1' is the only numeral this function would fetch the results for that particular row as it contains at least only one numeral.

To_number function will not be useful to do this. Oracle has a function called IsNumber which identifies whether a field value is numeric or else. Unfortunately this function would not solve the above problem as we are looking for a field with at least one numeral and not fully numeral.

We used translate and length functions to write such a query.

Query:

SELECT *

FROM TABLENAME

WHERE

LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) <> LENGTH(FIELDTOBECHECKED)

How it works?

LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) will give length of the field – FIELDTOBECHECKED accounting only the alphabetical characters and ignores any numerals.

LENGTH(FIELDTOBECHECKED) will give the total length of the string. In case there is at least only one numeral in the string then the left side expression will calculate the length of the string ignoring the numerals in it. As this would be not equal to the right side expression which is the total length of the string data for that row would be fetched.

Below I have explained this with 3 different examples.

Case 1:

In case the value of the FIELDTOBECHECKED is "MyString", the left side function would give length of 8 which is also equal to the actual length of the field 8(right side expression) and hence it does not fetch that row.

Case 2:

In case the value of the FIELDTOBECHECKED is "12071987", the left side function would give length of 0(zero) which is not equal to the actual length of the field 8(right side expression) and hence it does not fetch that row. This difference in length would fetch the results for that particular row as it contains at least all numerals.

If you are only looking for a condition to fetch data which has all numbers in a field then make the WHERE condition as LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789A','A')) is NULL.

Case 3:

In case the value of the FIELDTOBECHECKED is "My 1st String", the left side function would give length of 12 which is not equal to the actual length of the field 13(right side expression) and hence it does not fetch that row. Spaces are also considered in the length since '1' is the only numeral this function would fetch the results for that particular row as it contains at least only one numeral.

Thank you for sharing such a nice article. This article will surely enhance reader’s knowledge. Keep posting!

ReplyDelete