Home » SQL & PL/SQL » SQL & PL/SQL » how to display only char data only or numeric data only not both
how to display only char data only or numeric data only not both [message #615312] Mon, 02 June 2014 04:52 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,

i have one query pls give me some suggestions
suppose i have one table stud in that name colums contain data numeric and char type.
example- sachin,20,30,rohit,52,pravin
so write a query to display only char data only or numeric data only not both.

Thanks

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615313 is a reply to message #615312] Mon, 02 June 2014 04:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Search for [:digit:] and [:alpha:] character class.

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615317 is a reply to message #615313] Mon, 02 June 2014 05:20 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose that smart people here will suggest another (better) approach, but here's what I can think of: as you have values stored into a single column, first split them to rows (see my T_ROWS below) and then - using regular expressions i.e. its REGEXP_LIKE, along with classes Lalit suggested - fetch values you are interested in.

SQL> WITH test AS (SELECT 'sachin,20,30,rohit,52,pravin' col FROM DUAL),
  2       t_rows AS (    SELECT REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL) col
  3                        FROM test
  4                  CONNECT BY REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL)
  5                                IS NOT NULL)
  6  SELECT 'alphas' what, col
  7    FROM t_rows
  8   WHERE REGEXP_LIKE (col, '[[:alpha:]]')
  9  UNION ALL
 10  SELECT 'digits' what, col
 11    FROM t_rows
 12   WHERE REGEXP_LIKE (col, '[[:digit:]]')
 13  ORDER BY 1, 2
 14  ;

WHAT   COL
------ ----------------------------
alphas pravin
alphas rohit
alphas sachin
digits 20
digits 30
digits 52

6 rows selected.

SQL>

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615318 is a reply to message #615312] Mon, 02 June 2014 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Mon, 02 June 2014 10:52
example- sachin,20,30,rohit,52,pravin

Is that the contents of a single row of data, or is that 6 rows of data?

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615319 is a reply to message #615318] Mon, 02 June 2014 05:27 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Is that the contents of a single row of data, or is that 6 rows of data?


This is a single row of data..

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615320 is a reply to message #615319] Mon, 02 June 2014 05:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Then you'll need to split it (use one of the methods on this FAQ) into rows and apply what was suggested above.

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615321 is a reply to message #615317] Mon, 02 June 2014 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@Littlefoot,

This does not work, the result should be the words containing only alphabetic chars or digits, not both:
SQL> WITH test AS (SELECT 'michel56' col FROM DUAL),
  2       t_rows AS (    SELECT REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL) col
  3                      FROM test
  4                    CONNECT BY REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL)
  5                                 IS NOT NULL)
  6   SELECT 'alphas' what, col
  7     FROM t_rows
  8    WHERE REGEXP_LIKE (col, '[[:alpha:]]')
  9   UNION ALL
 10   SELECT 'digits' what, col
 11     FROM t_rows
 12    WHERE REGEXP_LIKE (col, '[[:digit:]]')
 13    ORDER BY 1, 2
 14    ;
WHAT   COL
------ --------
alphas michel56
digits michel56

2 rows selected.

So:
SQL> WITH test AS (SELECT 'sachin,20,30,rohit,52,pravin,michel56' col FROM DUAL),
  2       t_rows AS (    SELECT REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL) col
  3                      FROM test
  4                    CONNECT BY REGEXP_SUBSTR (col, '[^,]+', 1, LEVEL)
  5                                 IS NOT NULL)
  6   SELECT 'alphas' what, col
  7     FROM t_rows
  8    WHERE REGEXP_LIKE (col, '^[[:alpha:]]+$')
  9   UNION ALL
 10   SELECT 'digits' what, col
 11     FROM t_rows
 12    WHERE REGEXP_LIKE (col, '^[[:digit:]]+$')
 13    ORDER BY 1, 2
 14    ;
WHAT   COL
------ -------------------------------------
alphas pravin
alphas rohit
alphas sachin
digits 20
digits 30
digits 52

6 rows selected.

(Still assuming data is in one line and not 7 rows.)

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615322 is a reply to message #615320] Mon, 02 June 2014 05:35 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Then you'll need to split it (use one of the methods on this FAQ) into rows and apply what was suggested above.


Can you please provide me the sample code on this please?

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615324 is a reply to message #615322] Mon, 02 June 2014 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, Michel; I didn't pay attention to ^...$. Thank you!

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615325 is a reply to message #615312] Mon, 02 June 2014 07:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
 
SELECT  case
          when regexp_replace(column_value,'\d') is null then 'digits'
          when regexp_replace(column_value,'[[:alpha:]]') is null then 'alpha'
          when regexp_replace(column_value,'[[:alpha:]]|\d') is null then 'alphanumeric'
          else 'none of the above'
        end what,
        column_value col
  from  xmltable(
                 'ora:tokenize("sachin,20,30,rohit,52,pravin,michel56",",")'
                )
/

WHAT              COL
----------------- -----------
alpha             sachin
digits            20
digits            30
alpha             rohit
digits            52
alpha             pravin
alphanumeric      michel56

7 rows selected.

SQL>


SY.

Report message to a moderator

Re: how to display only char data only or numeric data only not both [message #615384 is a reply to message #615325] Tue, 03 June 2014 01:28 Go to previous message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks Solomon... Smile

Report message to a moderator

Previous Topic: How to partition Table Data Alphabetically
Next Topic: Invalid Synonym in SQL Plus Vs Toad
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 17:38:05 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.