Home » SQL & PL/SQL » SQL & PL/SQL » First character numeric, how ? (10g)
First character numeric, how ? [message #341192] |
Sun, 17 August 2008 19:00 |
icm63
Messages: 22 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 #341230 is a reply to message #341192] |
Sun, 17 August 2008 22:52 |
flyboy
Messages: 1903 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 |
icm63
Messages: 22 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 |
|
BlackSwan
Messages: 26766 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 |
flyboy
Messages: 1903 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).
|
|
|
|
Goto Forum:
Current Time: Sat Nov 09 13:01:50 CST 2024
|