how to extract alphnumeric from string colum in oracle

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 ?
RaoVPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Luis PérezSoftware Architect in .NetCommented:
0
RaoVPAuthor Commented:
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
Shaju KumbalathDeputy General Manager - ITCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaju KumbalathDeputy General Manager - ITCommented:

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
shru_0409Commented:
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
Shaju KumbalathDeputy General Manager - ITCommented:
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
sdstuberCommented:
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
Shaju KumbalathDeputy General Manager - ITCommented:
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
sdstuberCommented:
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
sdstuberCommented:
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
Shaju KumbalathDeputy General Manager - ITCommented:
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
RaoVPAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.