Please Help with SQL. (1 million records)

[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 (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 all letters and symbols on my keyboard. But what if its smth other than latin?

How would you suggest to find the record that is not exactly 7 digits long and/or contains symbols or letters?

Thanks in advance

 

[fredericofonseca]

Which database? This will determine the best option to you.


Apart from using the replace function e.g. replace each number individually by '' and then ignore all records where the lenght of the resulting string is 0 (zero) maybe try and catch an error on casting the variable as numeric. This last may still fail if the "symbol" is a ",", ".", "-", "+".

Eventually using a user defined function will be the best option.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Perhaps something like this ?
WHERE NOT (CHAR_LENGTH(membernum) = 7
AND SUBSTRING(membernum FROM 1 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 2 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 3 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 4 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 5 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 6 FOR 1) BETWEEN '0' And '9'
AND SUBSTRING(membernum FROM 7 FOR 1) BETWEEN '0' And '9'
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Necser]

Thanks for your reply,
This looks interesting, although I get an SQL error:
"Token Unknown - Line 2, Char 27
FROM "

Any idea?

 

[PHV]

Seems that your RDBMS is not ANSI compliant ...

 

[Necser]

I'm using Borland 6.5 and SQL Explorer 3.0

 

[PHV]

You have to know which function returns a portion of a string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Necser]

I ran
select 8 from customer WHERE NOT (CHAR_LENGTH(membernum) = 7);

And I get "Function Unknown CHAR_LENGTH

 

[Necser]

sorry...

select * from customer WHERE NOT (CHAR_LENGTH(membernum) = 7);

 

[PHV]

You have to know which function returns the length of a string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Necser]

I can't get any function to work that will return string length.
I can although use the function and delete the onese that are longer or shorter than 7
delete from customer where membernum < '0000001' or membernum > '09999999999'

What I'm left here is all membernum are 7 characters long. At this point I only need to select onese without only digits in membernum.

How about taking some approach to sort maybe calculate, compile digits in the customer number and the therefore distinguish from the ones without only digits.

Thanks

 

[Necser]

bump