Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to extract alphnumeric from string colum in oracle

Posted on 2010-01-12
12
Medium Priority
?
715 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
  • 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

580 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