Need Query Help To Identify VARCHAR Fields that only contain the charectors of zero thru nine (0-9)

frogman22
frogman22 used Ask the Experts™
on
I am working in SQL Server 2005 and am LINKED to an ORACLE database.

I need help writing a query that only returns what I am looking for. On the table I am quering there is a field called acct_nbr that is a varchar(25).  In that field there will be one of two types of entries. Type One - the field will only contain a value that consists of any combination of charecters of 0-9 and will always have a length of 9. Type two - The field will hold a value that has a combination of characters that consist of A-Z only.

I only want to return all records where the acct_nbr field contains charaters of only (0-9)

My current query returns results of:
record_num     acct_nbr
     1                123456789
     2                NNZ_XTYN

I only want to return record 1


This query does not work:
Select *
from openquery(db1,'
select *
from schema1.table_01
where acct_nbr like (''%0-9%'')
')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Both SQL Server and Oracle implement a form of regular expressions.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

I'm not a sql server person so I cannot provide the exact SQL the the regular expression should go something like '^[0-9]+$'
Top Expert 2010

Commented:
Hello frogman22,

You can create a CLR function for SQL Server that implements RegExp, but if you are *always* looking for 9
digit characters, this will work well enough:

where acct_nbr like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Regards,

Patrick
Top Expert 2010

Commented:
frogman22,

And if you want to see if the entry contains a 9-digit literal:

where acct_nbr like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Patrick

Author

Commented:
Patrick,

No Luck.  It did not work.

select *
from openquery(db1,'
select *
from schema1.table01
where access_date >= (''28-jun-2010'')
and access_date < (''29-jun-2010'')
and acc_log_id in (6325042,6325043)
and acc_info1 like (''[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'')
')

Author

Commented:
That query returns two results when the last line is commented out.

Result one for field acc_info1 = 702566301
Result two for field acc_info1 = NACUS_ZZM
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Again, I'm not a SQL Server guy but if openquery passes that SQL off to Oracle, try the Oracle regexp call:

select *
from openquery(db1,'
select *
from schema1.table01
where access_date >= (''28-jun-2010'')
and access_date < (''29-jun-2010'')
and acc_log_id in (6325042,6325043)
and regex_like(acc_info1,''^[0-9]+$'')
')

Author

Commented:
The follwoing error occurs:
 "ORA-00920: invalid relational operator when using and regex_like(acc_info1,''^[0-9]+$'')
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I might have the syntax a little off.

I'm in a meeting now.  Google around a little until I can get back to a real keyboard.

Author

Commented:
ok.  AM reading the previous link trying to figure out
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I also thought of another way that might work for you.

Here's two working examples using strictly Oracle syntax and tested on version 10.2.0.3.

Hopefully one of these will pass through SQL Server.
drop table tab1 purge;
create table tab1(col1 varchar2(20));

insert into tab1 values('123456789');
insert into tab1 values('NNZ_XTYN');
commit;

select col1 from tab1 where regexp_like(col1,'^[0-9]+$');
select col1 from tab1 where trim(translate(col1, '1234567890','          ')) is null;

Open in new window

Author

Commented:
It works!!!!
select col1 from tab1 where regexp_like(col1,'^[0-9]+$');

It appears the letter "P" was missing from regexP in the first example.:-)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad it worked.  Yep..typo.  That's what I get for not actually setting 'test' code!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial