Forum Stats

  • 3,768,167 Users
  • 2,252,755 Discussions
  • 7,874,480 Comments

Discussions

data case sensitive Validation

User_7PZDE
User_7PZDE Member Posts: 282 Blue Ribbon
edited Jun 1, 2017 11:05AM in Forms

Hi Team

I have scenario to check the data case sensitive i.e

I have table called parther_grouping

create table parther_grouping  ( Parther_Main_Group Varchar2(100));

insert into parther_grouping values ('Data Main');

insert into parther_grouping values ('Data Main');

insert into parther_grouping values ('Oracle');

insert into parther_grouping values ('data Main');

select Parther_Main_Group  from parther_grouping ;

Data Main

Data Main

Oracle

data Main

If see above column Parther_Main_Group i have group of Data Main with 3 and One is small letter *d*(data Main)

i need to find those records it is just example script i have around 10,000 recrods Parther_Main_Group

Version Oracle 9i

Thanks in Advance

Tagged:

Answers

  • Hendrik Schmidt
    Hendrik Schmidt Member Posts: 353
    edited May 31, 2017 7:38AM

    Hi,

    good question for: select Parther_Main_Group from parther_grouping where Parther_Main_Group = 'data Main'

    you receive only the record with the small 'd".

    If you know the used combinations you can use this in the select, but for General, perhaps you will receive an answer frrom another People.

    regards

  • User_7PZDE
    User_7PZDE Member Posts: 282 Blue Ribbon
    edited May 31, 2017 8:04AM

    Hendrik

    I just give the example data Main but if you have data 10,000 records and i try using group by condition on column Parther_Main_Group

    still not getting as expected . I just want to know if there is different case sensitive

    Thanks

  • Wayne_Duke
    Wayne_Duke Member Posts: 26 Blue Ribbon
    edited May 31, 2017 8:31AM

    I am not sure quite what you mean, but have you considered using the regexp_like function in the where clause. with this you can set regular expressions and it can be case sensitive.

  • Gary_A
    Gary_A Member Posts: 624 Bronze Badge
    edited May 31, 2017 2:18PM

    I don't quite understand your question, but is this what you are looking for?

    select Parther_Main_Group,count(*)from parther_groupinggroup by Parther_Main_Group;
  • Andreas Weiden
    Andreas Weiden Member Posts: 10,868 Gold Crown
    edited May 31, 2017 11:57AM

    This is more a SQL/PLSQL-question than a forms-question.

    If you want to find "duplicates" which are just different in the casing, you can use

    SELECT UPPER(Parther_Main_Group), COUNT(*)  FROM parther_grouping GROUP BY UPPER(Parther_Main_Group)HAVING COUNT(*)>1;
  • User_7PZDE
    User_7PZDE Member Posts: 282 Blue Ribbon
    edited May 31, 2017 2:27PM

    Thanks But

    I am not looking for group by condition , i am finding case sensitive records i.e

    with wel as (

    select 'Oracle' ac from dual

    union all

    select 'Oracle' from dual

    union all

    select 'oracle' from dual

    union all

    select 'Java' from dual

    union all

    select 'Java' from dual

    union all

    select 'Java' from dual

    )

    select ac,count(*) from wel

    group by ac

    output

    Oracle2
    Java3
    oracle

    1

    but i required Output having case sensitive  records in data

    Oracle 2

    oracle  1

  • Andreas Weiden
    Andreas Weiden Member Posts: 10,868 Gold Crown
    edited Jun 1, 2017 11:05AM

    You still can use the groupinp approach:

    SELECT *  FROM parther_grouping WHERE UPPER(Parther_Main_Group) IN (SELECT UPPER(Parther_Main_Group)                                         FROM parther_grouping                                        GROUP BY UPPER(Parther_Main_Group)                                        HAVING COUNT(*)>1                                     );
This discussion has been closed.