Home » SQL & PL/SQL » SQL & PL/SQL » First character numeric, how ? (10g)
First character numeric, how ? [message #341192] Sun, 17 August 2008 19:00 Go to next message
icm63
Messages: 21
Registered: December 2007
Junior Member
HI,

I have data that may or may not start with a number. I wish to count the records that start with a number (0-9)?

7 Perry Place
Greenlane
10 Hope street
North west street
8 Kelly View

So the count here would be 3

Something like this,maybe ??? Whats the correct PLSQL to solve this little test.

SELECT ISNUMERIC(LEFT(TRIM([Field]),1)) FROM [DataTable]

Thanks in advance
Re: First character numeric, how ? [message #341193 is a reply to message #341192] Sun, 17 August 2008 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: First character numeric, how ? [message #341230 is a reply to message #341192] Sun, 17 August 2008 22:52 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It seems you shall get acknowledged with Oracle in-built functions. They are listed in SQL Reference, which is part of Oracle documentation, found e.g. online on http://tahiti.oracle.com/.

For getting the first character, concentrate on "Character Functions Returning Character Values".
I do not know about "ISNUMERIC" function, but for the check you may use "IN condition" with all 10 numbers.
Re: First character numeric, how ? [message #341233 is a reply to message #341230] Sun, 17 August 2008 23:16 Go to previous messageGo to next message
icm63
Messages: 21
Registered: December 2007
Junior Member
Why do I need to read the guidle lines for posting ??

So IN(0,1,2,3,4,5,6,7,8,9), is this what you mean ??
Re: First character numeric, how ? [message #341235 is a reply to message #341192] Sun, 17 August 2008 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Why do I need to read the guidle lines for posting ??
You do not need to follow guideline for posting.
For some/many/most you should follow guidelines to get answers.

>So IN(0,1,2,3,4,5,6,7,8,9), is this what you mean ??
Does this solve your problem for all cases/values? I do not think so.
Re: First character numeric, how ? [message #341246 is a reply to message #341192] Sun, 17 August 2008 23:40 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
>So IN(0,1,2,3,4,5,6,7,8,9), is this what you mean ??

Not exactly.
Firstly, you shall get only the first character as stated in the first paragraph.
Secondly, I was not exact. 0 is number. You shall not compare character with number, as it leads to implicit conversion and (most probably) error on non-numeric characters. You shall compare string with string representation of number ('0' for example).
Re: First character numeric, how ? [message #341303 is a reply to message #341192] Mon, 18 August 2008 04:47 Go to previous message
sumanthd
Messages: 10
Registered: June 2008
Location: Hyderabad
Junior Member

hi
Try this Quiery

SELECT count(*) 
FROM [DataTable] 
where REGEXP_like([Field],'^[0-9]{1}');


njoy
Previous Topic: ORA 24381
Next Topic: Interval to Seconds
Goto Forum:
  


Current Time: Fri Dec 09 00:00:20 CST 2016

Total time taken to generate the page: 0.20211 seconds