-1

I need to write sql query for get similar records from table.It has 100 of records with difference of values. But some records have similar name.Similar names start with capital or simple.

ex- I want to get all the similar records who has similar names. 

Student_no | student_Name | Subject   |class
  1        | Abc          | Java      | 1
  2        | Abc          | Java      | 1
  3        | xyz          | AngularJS | 2 
  4        | xyz          | AngularJS | 2
  5        | Abc Def      | SpringBoot| 3
user5702902
  • 43
  • 1
  • 10

2 Answers2

1

Use EXISTS and the LIKE operator:

select t.*
from tablename t
where exists (
  select 1 from tablename
  where
    Student_no <> t.Student_no
    and (
      student_Name like concat(t.student_Name, '%')
      or
      t.student_Name like concat(student_Name, '%')
    )
)

See the demo.
Results:

| Student_no | student_Name | Subject    | class |
| ---------- | ------------ | ---------- | ----- |
| 1          | Abc          | Java       | 1     |
| 2          | Abc          | Java       | 1     |
| 3          | xyz          | AngularJS  | 2     |
| 4          | xyz          | AngularJS  | 2     |
| 5          | Abc Def      | SpringBoot | 3     |
forpas
  • 160,666
  • 10
  • 38
  • 76
0

In your instance, a statement like:

SELECT * FROM TABLE WHERE UPPER(student_Name) like UPPER('xyz')

would shift all student_Name values to uppercase, then retrieve all uppercase (or, just all) "XYZ" values.

If not all names are like "xyz" (or, some are "xyz1", "1_xyz", or "ABCXYZDEF"), you need to use like with % representing any number of missing values:

SELECT * FROM TABLE WHERE UPPER(student_Name) like UPPER('%xyz%')

Please see the attached link for more information: SQL- Ignore case while searching for a string

Canbo
  • 113
  • 8