?
Solved

how to extract alphnumeric from string colum in oracle

Posted on 2010-01-12
12
Medium Priority
?
707 Views
Last Modified: 2013-12-19
I need to separate the numeric and alphabetic values from a single column (of string data type)of a table
ex:
column A
----------
123
aaa
a12
1b7
dd4
134

How to extract
only numeric values
only alphabetic values
only alphanumeric values
from this column ?
0
Comment
Question by:RaoVP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 26291069
0
 

Author Comment

by:RaoVP
ID: 26291215
thanks roland,
but I need to list out all the numeric values, aplha, alphanumeric separately from a single column of the table example mentioned above
column A
----------
123
aaa
a12
1b7
dd4
134

Into comma separated or in a list. how to extract these values separately.
123, 134
a12, 1b7, dd4
aaa

Thanks n advance
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 501 total points
ID: 26291526

SELECT (case
when length(REGEXP_SUBSTR(your_col,'*[1-9]+') ) >0 then
(case
when length(REGEXP_SUBSTR(your_col,'*[a-z]+') )> 0 then
'alphanumeric'
else
'numeric'
end )
else
'alpha'
end )
FROM your_table;
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26291546

SELECT LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || col1)), '/x/text()'),
','
)
FROM your_table
GROUP BY (case
when length(REGEXP_SUBSTR(col1,'*[1-9]+') ) >0 then
(case
when length(REGEXP_SUBSTR(col1,'*[a-z]+') )> 0 then
'alphanumeric'
else
'numeric'
end )
else
'alpha'
end )
0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 501 total points
ID: 26291598
select nmbr,LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || disc)), '/x/text()'),',' ) from (
SELECT 1 nmbr , REGEXP_SUBSTR(testcol,'^[[:digit:]]*') disc
FROM test
union all
SELECT 2, REGEXP_SUBSTR(testcol,'^[[:alnum:]]*')
FROM test
union all
select 3, REGEXP_SUBSTR(testcol,'^[[:alpha:]]*')
FROM test ) z
group by nmbr

try this
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26291989
For handling case sensitive issues use
SELECT LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || col1)), '/x/text()'),
','
)
FROM your_table
GROUP BY (case
when length(REGEXP_SUBSTR(lower(col1),'*[1-9]+') ) >0 then
(case
when length(REGEXP_SUBSTR(lower(col1),'*[a-z]+') )> 0 then
'alphanumeric'
else
'numeric'
end )
else
'alpha'
end )
 
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26292850
since this is for version 9i,  none of the regular expressions suggested above will work (you'll need 10g for that).

For 9i  try something like this...
SELECT stringtype,
       RTRIM(
           REPLACE(REPLACE(XMLAGG(XMLELEMENT("x", a) ORDER BY a).getstringval(), '<x>', NULL), '</x>', ','),
           ','
       )
           strings
FROM (SELECT a, CASE NVL(LENGTH(TRANSLATE(a, CHR(0) || '1234567890', CHR(0))), 0)
                    WHEN 0 THEN 'numeric'
                    WHEN LENGTH(a) THEN 'alphabetic'
                    ELSE 'alphanumeric'
                END
                    stringtype
      FROM yourtable)
GROUP BY stringtype
ORDER BY stringtype;

Open in new window

0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26293127
Dear SD,
i wanted to know why u used chr(0) in translate
i tried some sqls

select translate('ABC1D ',CHR(0)||'1234567890', CHR(0)) FROM DUAl
output ABCD

select translate('ABC1D ','1234567890', CHR(0)) FROM DUAL
OUTPUT ABC
Can u please explain me why?
 
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 498 total points
ID: 26293218
translate is a mass-replace with character-by-character matching between the 2nd and 3rd parameters.

any characters of the 2nd string will be replaced with null if they do not have a corresponding match in the 3rd string.

so, in your first example.  you are replacing chr(0) with chr(0) and replacing all digits with null.    Since your string doesn't have chr(0), no translation occurs for that, but you do have a digit so it's replaced with null and it drops out of the result.

in your second example you are replacing 1 with chr(0) and all other digits are replaced with null.

chr(0) is the null character, or string terminator character.
you still have 6 characters in your string  (try this to check)

select length(translate('ABC1D ','1234567890', CHR(0))) FROM DUAL

but when you display it, the string is ended at the 0 so you don't see the rest of the characters.

That's why I pick chr(0) as a "dummy" character.  It is possible that the varchar2 column could have chr(0) in it, but it's an unlikely choice.    I could pick '!'  or any other character that isn't alphanumeric.

hope that helps






0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26293285
also note, the "isnumeric" method suggested in the first post has some holes in it.

it can give false positives, for instance...


select LENGTH(TRIM(TRANSLATE ('-+-+-+-+-+-+-+-+-+-+', ' +-.0123456789',' '))) from dual

this returns null, which, according to the linked page above, means it's a number but it's clearly not.

a very simple and more reliable method is to use the built in TO_NUMBER function to try convert a string to a number,  if it works then it's a number, if it doesn't then it's not.  

something like this...


CREATE OR REPLACE FUNCTION is_number(p_str IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_dummy NUMBER;
BEGIN
    v_dummy := TO_NUMBER(p_str);
    RETURN 'Yes';
EXCEPTION
    WHEN VALUE_ERROR
    THEN
        RETURN 'No';
END;

Open in new window

0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26293407
Thanks SD , i got it , after the digit string terminates as chr(0) is not part of the string,
Thanks once again, "Wish u a happy New Year"
-- Shaju
0
 

Author Closing Comment

by:RaoVP
ID: 31675965
Thanks a lot
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question