Please Help with SQL Query.

[Necser]

Hi,
I have a customer table with ~1 million records.

I have an external application that performs sertain task to the member number. It requires that the member number is exactly 7 DIGITS long (no letters, simbols, only digits) (table is alphanumeric).

There is a record in that table that either has a letter or a symbol.

I was using like '%x%' command where x was every letters and symbols on my keyboard I could find. But what if its smth other than latin?

How would you suggest to find the record that might contain something other than all digits?

Table = CUSTOMERS
Field= MEMBERNUM

Thanks in advance

 

[rtmpxr]

I found a solution which works on my SQL engine (Firebird 1.5). There it is:

select <primary key>,substring(MEMBERNUM from 1 for 1) from CUSTOMERS where substring(MEMBERNUM from 1 for 1) not between '0' and '9'

SUBSTRING extracts one character from MEMBERNUM and check if this charcater is between 0 and 9 (pay attention that '0' is character 0 and not number 0; same for '9'). Th syntax is:
SUBSTRING( <string expr> FROM <pos> [FOR <length>]).
<primary key> is used to identify the record.
Then you can test for the second position in MEMBERNUM like:

select <primary key>,substring(MEMBERNUM from 2 for 1) from CUSTOMERS where substring(MEMBERNUM from 2 for 1) not between '0' and '9'

and so on until you find that charcter which gives headaches.

HTH

 

[Necser]

select <primary key>,substring(MEMBERNUM from 1 for 1) from CUSTOMERS where substring(MEMBERNUM from 1 for 1) not between '0' and '9'


This gives me an SQL error for the first "FROM"..."Token unknown"

Any ideas?

 

[johnwm]

You probably need to specify which SQL you are using. The syntax for SUBSTRING in MSSQL is:
SUBSTRING(MemberNum,1,1)

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[Necser]

Thanks for your responds.

How do I check which SQL am I running?

We have Borland 6.5 and SQL Explorer 3.0.

 

[rtmpxr]

Borland 6.5 means, I believe, Interbase 6.5. In this case search for SUBSTR function in UDF library. It is there.