newbie at sql. Need select for only numeric values in a column

[scrsadmin]

I need to do a select against iadocument where maindex1 is only 9 digits. It has to be only 9 and can only be digits no alphabet.
I don’t want it to return a row if the data has more or less then 9 digits and it has to be only numeric in value.

 

[gmmastros]

what have you tried so far?

There are a couple things that may be helpful.

I would suggest:
Like
Len
IsNumeric.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[scrsadmin]

Heres what i came up with.

select * from tablename where TRANSLATE(column_name, '0123456789','XXXXXXXXXX') <> 'XXXXXXXXX'

 

[gmmastros]

TRANSLATE is not a Microsoft SQL Server function. Are you using ORACLE? If so, I suggest you post your question in the ORACLE forum.

Here: forum1177


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

And from where TRANSLATE comes from?
That is not T-SQL function.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JackVam]

In SQL Server you would do:

DECLARE @tmpNumber INT
SET @tmpNumber = 123456789

IF ISNUMERIC(@tmpNumber) = 1 AND LEN(@tmpNumber) = 9
PRINT 'yes'

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[scrsadmin]

Sorry, I thought SQL statements worked on both Sql server and Oracle Server the same. Thanks for all your input.