Please Help with SQL Query.
Please Help with SQL Query.
(OP)
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
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





RE: Please Help with SQL Query.
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
RE: Please Help with SQL Query.
This gives me an SQL error for the first "FROM"..."Token unknown"
Any ideas?
RE: Please Help with SQL Query.
SUBSTRING(MemberNum,1,1)
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Please Help with SQL Query.
How do I check which SQL am I running?
We have Borland 6.5 and SQL Explorer 3.0.
RE: Please Help with SQL Query.