Home » SQL & PL/SQL » SQL & PL/SQL » First Character in "numeric-Alpha" ( 10.2.0)
First Character in "numeric-Alpha" [message #607629] Mon, 10 February 2014 06:25 Go to next message
bluetooth420
Messages: 77
Registered: November 2011
Member
Dear All,

I need to know the position of first character in the given "numeric-aplha" string which is in following format.
9999-9999AAAAAAAA.

Basic problem is that numeric portion is not always 4+4 wide. It may range from 1 to 9999. Hence following are possible numeric-alpha examples
411-123 TEST >> required answer 8
1-151 ANOTHER TEST >> required answer 6
1151-1 ONE MORE TEST >> >> required answer 7
1151-147LAST TEST >> >> required answer 8
SPECIAL TEST >> >> required answer 1


The line of action which i have though is
1) with loop and substr, start checking each character after first occurrence of "-"/ (or from first character if "-" not found.
2) If the character under checking is numeric then check next unless i find non-numeric

I can do it but looking for a possible solution in REGEXP_INSTR .

Moreover, can someone guide me simple tutorial of REGEXP....

Thanks,



Re: First Character in "numeric-Alpha" [message #607630 is a reply to message #607629] Mon, 10 February 2014 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with data as (
  2  select '411-123 TEST' val from dual union all
  3  select '1-151 ANOTHER TEST' val from dual union all
  4  select '1151-1 ONE MORE TEST' val from dual union all
  5  select '1151-147LAST TEST' val from dual union all
  6  select 'SPECIAL TEST' val from dual
  7  )
  8  select val, regexp_instr(val,'[^0-9-]') ind from data;
VAL                         IND
-------------------- ----------
411-123 TEST                  8
1-151 ANOTHER TEST            6
1151-1 ONE MORE TEST          7
1151-147LAST TEST             9
SPECIAL TEST                  1

Re: First Character in "numeric-Alpha" [message #607632 is a reply to message #607630] Mon, 10 February 2014 06:43 Go to previous messageGo to next message
bluetooth420
Messages: 77
Registered: November 2011
Member
Bravooo !!!

How can i learn? some easy tutorial?
Re: First Character in "numeric-Alpha" [message #607634 is a reply to message #607632] Mon, 10 February 2014 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Regular expressions are not special to Oracle you can find tutorials on the web about them.
For this expression "[^0-9-]":
1) "[]" specifies a list of characters
2) when this list starts with "^" we want a character that is not in the list
3) "0-9" is an interval, it means any character between "0" and "9"
So "[^0-9-]" means not ("^") a character that is between "0" and "9" ("0-9") or a "-" (last character of the list, this "-" means the character "-" and not an interval as it is not followed by another character)

Re: First Character in "numeric-Alpha" [message #607648 is a reply to message #607634] Mon, 10 February 2014 10:01 Go to previous messageGo to next message
bluetooth420
Messages: 77
Registered: November 2011
Member
Thank u so much. I saw a complex tutorial which made me demotivated to learn. Thanks for your easy explaination
Re: First Character in "numeric-Alpha" [message #607664 is a reply to message #607648] Mon, 10 February 2014 15:13 Go to previous messageGo to next message
bluefred
Messages: 16
Registered: June 2012
Location: Qatar
Junior Member
just in case you have forgotten to accommodate for the space, use '[^ 0-9-]' or '[^0-9 -]'

WITH D AS 
(SELECT '1234-32 1A' STR FROM DUAL UNION ALL
 SELECT '1234-32 ABC' STR FROM DUAL UNION ALL
 SELECT 'SS1234-32 1A' STR FROM DUAL)
SELECT STR,REGEXP_INSTR(STR,'[^ 0-9-]') OUTPUT
FROM D;

STR                   OUTPUT
------------ ----------
1234-32 1A           10 
1234-32 ABC           9 
SS1234-32 1A          1 
Re: First Character in "numeric-Alpha" [message #607665 is a reply to message #607664] Mon, 10 February 2014 15:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2064
Registered: January 2010
Senior Member
Instead of looking for what to exclude why not simply to look for alphas:

WITH D AS 
(SELECT '1234-32 1A' STR FROM DUAL UNION ALL
 SELECT '1234-32 ABC' STR FROM DUAL UNION ALL
 SELECT 'SS1234-32 1A' STR FROM DUAL)
SELECT STR,REGEXP_INSTR(STR,'[[:alpha:]]') OUTPUT
FROM D
/

STR              OUTPUT
------------ ----------
1234-32 1A           10
1234-32 ABC           9
SS1234-32 1A          1

SCOTT@pdb1orcl12 >


SY.
Re: First Character in "numeric-Alpha" [message #607667 is a reply to message #607665] Mon, 10 February 2014 15:43 Go to previous messageGo to next message
bluefred
Messages: 16
Registered: June 2012
Location: Qatar
Junior Member
Quote:

Instead of looking for what to exclude why not simply to look for alphas:

REGEXP_INSTR(STR,'[[:alpha:]]') 
=> way much better and readable

[Updated on: Mon, 10 February 2014 15:46]

Report message to a moderator

Re: First Character in "numeric-Alpha" [message #608289 is a reply to message #607667] Tue, 18 February 2014 06:55 Go to previous messageGo to next message
bluetooth420
Messages: 77
Registered: November 2011
Member
Just for learning

If want to select all males then which method is fine in where statement.
(assuming that there is M/F in the field sex)


1) where sex='M'
2) where sex!='F'




Re: First Character in "numeric-Alpha" [message #608290 is a reply to message #608289] Tue, 18 February 2014 07:03 Go to previous message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
depends if sex is nullable, if it is only 1 will work. I would always use 1 regardless.
Previous Topic: Oracle Compare 2 Tables
Next Topic: Alter Table Command in relation to another table primary key
Goto Forum:
  


Current Time: Wed Nov 26 20:08:53 CST 2014

Total time taken to generate the page: 0.12365 seconds