Home » SQL & PL/SQL » SQL & PL/SQL » finding a charachter in a string
finding a charachter in a string [message #185071] Sun, 30 July 2006 20:51 Go to next message
mary10k
Messages: 4
Registered: November 2005
Junior Member
Hi,

I am trying to find any account numbers who's 10th digit is not 0. The field is 16 charachters long and the first 9 digits represent an account.

I tried the following SQL, but I do not think this is correct.

Select account_num, substring(account_num,1,9)
where substring(account_num,1,9)<>0;

This is not returning the correct results. could you please give me some direction?

Thank you.
Re: finding a charachter in a string [message #185075 is a reply to message #185071] Sun, 30 July 2006 21:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
where substring (account_num, 10, 1) <> 0
Re: finding a charachter in a string [message #185148 is a reply to message #185071] Mon, 31 July 2006 04:08 Go to previous messageGo to next message
ruyue
Messages: 9
Registered: July 2006
Junior Member
Select account_num, substr(account_num,1,9)
where substr(account_num,10,1)<>0;
Re: finding a charachter in a string [message #185296 is a reply to message #185071] Tue, 01 August 2006 01:52 Go to previous messageGo to next message
anand_mohanty
Messages: 2
Registered: July 2006
Location: Mumbai
Junior Member

Select account_num, substr(account_num,1,9)
from tablename
where substr(account_num,10,1)<>0;
Re: finding a charachter in a string [message #185626 is a reply to message #185071] Wed, 02 August 2006 14:27 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Just being picky, do not force the sql engine to do an implicent conversion. It slows (very little) the query.

Select account_num, substr(account_num,1,9)
from tablename
where substr(account_num,10,1)<>'0';
Previous Topic: Query
Next Topic: Procedure to validate an IP Address
Goto Forum:
  


Current Time: Sun Dec 04 08:56:37 CST 2016

Total time taken to generate the page: 0.10447 seconds