how to search a column containg alphabets?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to search a column containg alphabets?

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    how to search a column containg alphabets?

    hi friends,

    can u plz give the query, to search a column which consists of
    alphabets or alphanumeric values. But it should not display
    the rows with only numbers. The column datatype is varchar2.

    for e.g the bill_no column contains the values
    'RAJ', 'JOHN', '12870', '2000', 'JOHN35'
    the query should display only 'RAJ','JOHN','JOHN35'.


    with thanks

    by madhu

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm not writing your query but I'll point your nose in the right direction.

    Time ago I found this beauty in some place...
    Code:
    select instr(translate('1234',
           'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
    	   'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
    from   dual;
    ...it returns Zero when number, non-Zero value when there is anything in there that is not a number.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width