Finding numbers in strings 1

[Guest_imported]

Hello, I must find the elements in a table where an alfanumeric field has just numbers within. In other words, detect the strings formed only by numbers.

 

[dickiebird]

String and conversion functions are very depending on the Database - what are you working with ? Dickie Bird
db@dickiebird.freeserve.co.uk

 

[Guest_imported]

Sorry, I forgot to say the database, it's Oracle.

 

[angiole]

Since Oracle does automatic casting (to a point), you might try, for a field length of 5, the following:

where col1 between 00000 and 99999.

Of course, you may have to justify right, or left, either way count on being busy this weekend.

AA 8~)

 

[Guest_imported]

angiole, thanks, I've tried your sugestion, with the following query:

select * from table where lpad(col1, 7, 0) between '0000000' and '9999999'

but it didn't work, and I must put the ' in the values. The field length is variable, then I choose the max value (7) to justify the string. But all the strings with a length < 7 are showed. Any other ideas?

 

[carp]

SQL> select * from test;

X
-------
12345
quack5
moo

select * from test
where
translate(x,'1234567890abcdefghijklmnopqrstuvwxyz',
'1234567890') = x;

X
-------
12345

 

[karluk]

I think Carp is on the right track, but his suggestion won't work if the data contains other characters in addition to letters and numbers. A more general approach is

select * from your_table where ltrim(your_column,'0123456789') is null;

The ltrim eliminates all leading numeric digits from the data. If all that is left is a null string, you know that you started with an all numeric string.

This method probably also works if by &quot;numbers&quot; you mean numeric strings that contain a decimal point. It looks as if you can just add the extra valid character to the ltrim:

select * from your_table where ltrim(your_column,'0123456789.') is null;

 

[Guest_imported]

Thank you all, people! Your query solved my question perfectly, karluk.

 

[karluk]

Actually, on second thought you have to be careful with decimal points. If you allow them, you also have to check that there is at most one in the string. E.g. &quot;1.5&quot; is numeric, but &quot;1.2.3&quot; is not. There appear to be several strategies that would deal with this issue, but I won't pursue it unless you need a solution.

 

[angiole]

Good One Karluk... AA 8~)