Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

comparison query using like\between

Status
Not open for further replies.

shyamal

Programmer
Aug 14, 2000
79
US
I have a field which can holds string values which are alphanumeric. I would like to filter the field so that I return only the alpha rows.

Table = Customer
Field = AcctNum

Suppose that these are the rows:

AcctNum
123456
ABC
ZTL
G23456
9955AC

By doing the following query:
Select * from customer where AcctNum between 'A' and 'Z'
returns ABC,ZTL and of course G23456,9955AC

How can I refine the query so that it returns only ABC and ZTL?

Thanks in advance
 
In the example above you are only interested in the first charecter so...

substr(acctnum,1,1) between 'A' and 'Z'

would probably do it, but I have a feeling that's not what you want!!
 
Oh Gawd, someone please tell me there is an easier way than the one I just figured out:
Code:
SELECT * from Customer
WHERE INSTR(AcctNum, '0', 1) = 0 AND 
      INSTR(AcctNum, '1', 1) = 0 AND 
      INSTR(AcctNum, '2', 1) = 0 AND 
      INSTR(AcctNum, '3', 1) = 0 AND 
      INSTR(AcctNum, '4', 1) = 0 AND 
      INSTR(AcctNum, '5', 1) = 0 AND 
      INSTR(AcctNum, '6', 1) = 0 AND 
      INSTR(AcctNum, '7', 1) = 0 AND 
      INSTR(AcctNum, '8', 1) = 0 AND 
      INSTR(AcctNum, '9', 1) = 0
There has to be a better way. This is going to run the INSTR function ten times for each row in the database.
Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?
 
I think one can probably use patindex to search for strings only. What do you think?
 
This is the best I was able to do and it does work. I just am not happy with the last and statement.


select * from customer
where patindex(&quot;%[a-z][a-z][a-z]%&quot;,acctnum)<>0
and acctnum not like '%[0-9][0-9][0-9][0-9][0-9]%'

The last like statement was driven by the fact that when I executed the query the last few rows returned acctnums which had numbers but also had three consecutive alpha characters qualified by the first part of the where statement.
 
What flavor of SQL are you using? Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top