×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Please Help with SQL Query.

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

RE: Please Help with SQL Query.

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

RE: Please Help with SQL Query.

(OP)
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?

RE: Please Help with SQL Query.

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: www.essexsteam.co.uk

RE: Please Help with SQL Query.

(OP)
Thanks for your responds.

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources