Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Query to retrive only those entries which are having special char

    Hi ,

    I have a table which is having a column 'COLA' having varchar (20) but some of the entries are having special chars like * & % ( ) - @ etc.

    I have created a query :
    select distinct COLA
    from
    MyTable
    where
    COLA like '%:%'
    OR COLA like '%(%'
    OR COLA like '%(%'
    OR COLA like '%-%'
    OR COLA like '%)%'
    OR COLA like '%''%'
    OR COLA like '%&%';

    but i have take account for each and every special character which is not a very efficient way of writing a query. so please advice.

    I tried the regular expression also ::
    select distinct COLA
    from
    MyTable
    where REGEXP_LIKE(GRPNAMSRH,'[a-z][A-Z][0-9]','i');
    but its not working .......


    Please experts ... please advice ...

    Cheers!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by subhotech View Post
    but its not working .......
    That is not a valid Oracle error message.

  3. #3
    Join Date
    Jan 2010
    Posts
    64
    both the query are working fine .. but i am not getting the desired results in the second query (i.e regexp_like) ........

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Try:

    Code:
    regexp_like(GRPNAMSRH, '^[a-z0-9]+$', 'i')
    Btw: you don't need to specify [a-z] and [A-Z] if you use the case-insesitive mode (by specifying the 'i' in regexp_like)

  5. #5
    Join Date
    Jan 2010
    Posts
    64
    Thanks !! it is working fine .....

    But i just need those which are having the special chars ..

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by subhotech View Post
    But i just need those which are having the special chars ..
    The simply negate the query:
    Code:
    WHERE NOT regexp_like(GRPNAMSRH, '^[a-z0-9]+$', 'i')

  7. #7
    Join Date
    Jan 2010
    Posts
    64

    Thanks for the help !!

    Thanks for the help !!

Posting Permissions

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