How to get similar records using SQL

Question

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

forpas · Accepted Answer · 2019-05-30 17:23:19Z

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     |

edited May 30, 2019 at 17:23

answered May 30, 2019 at 17:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Canbo · Accepted Answer · 2019-05-30 17:13:32Z

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

edited May 30, 2019 at 17:13

answered May 30, 2019 at 17:02

Canbo

1138 bronze badges

1 Comment

user5702902 Over a year ago

Thank-you @CannonJ95. But I have different names. I can't change the query's like part one by one.

Collectives™ on Stack Overflow

How to get similar records using SQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related