Replace/Update multiple field values into a single value using sql

Question

my table is like:

id |  name |   subject
-------------------
1  | kil   |   btech
2  | Jim   |   BACHELOR OF TECHNOLOGY
3  | Joe   |   Btech
4  | Ruth  |    MCA
5  | priya |   Master in comp application
6  | dev   |   MASTER IN COMPUTER APPLICATION
7  | sen   |   Master in Computer Application

how to group related field value into a single name such as:

id |  name  |  subject
-------------------
1  | kil    |  BACHELOR OF TECHNOLOGY
2  | Jim    |  BACHELOR OF TECHNOLOGY
3  | Joe    |  BACHELOR OF TECHNOLOGY
4  | Ruth   |  Master in Computer Application
5  | priya  |  Master in Computer Application
6  | dev    |  Master in Computer Application
7  | sen    |  Master in Computer Application

Jayasurya Satheesh · Accepted Answer · 2017-12-19 06:51:27Z

2

Create a table With Synonyms of the same course. And then join These two tables to get the result

(this might be the easiest way since you can add more Synonyms later without code change)

Like this

CREATE TABLE dbo.t_Synonyms
(
    SeqNo INT IDENTITY(1,1),
    FalseText VARCHAR(255),
    DisplayText VARCHAR(255)
)

INSERT INTO t_Synonyms(FalseText,DisplayText)
VALUES('BTEC','BACHELOR OF TECHNOLOGY'),
('B.TECG','BACHELOR OF TECHNOLOGY'),
('MCA',' Master in Computer Application'),
    ('Master in comp application',' Master in Computer Application')
SELECT
    YT.ID,
    YT.NAME,
    [SUBJECT] = ISNULL(TS.DisplayText,YT.[SUBJECT])
    FROM YourTable YT
        LEFT JOIN t_Synonyms TS
            ON LTRIM(RTRIM(YT.[SUBJECT])) = LTRIM(RTRIM(TS.FalseText))

edited Dec 19, 2017 at 6:51

answered Dec 19, 2017 at 6:47

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

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

2 Comments

Jayasurya Satheesh Over a year ago

@Schuere SQL is case insensitive, so you don't need that

Damien_The_Unbeliever Over a year ago

The default options for installation of SQL Server will tend to leave you with a server whose default collation is case insensitive. That a) doesn't mean that you'll never encounter a case sensitive collation when working with SQL Server and b) definitely doesn't mean that "SQL is case insensitive".

Ravi · Accepted Answer · 2017-12-19 10:14:12Z

0

update mytable set subject = case when subject = 'btech' or subject = 'Btech' then 'BACHELOR OF TECHNOLOGY' when subject = 'MCA' or subject = 'Master in comp application' then 'Master in Computer Application' end where subject = 'btech' or subject = 'Btech' or subject = 'MCA' or subject = 'Master in comp application'

answered Dec 19, 2017 at 10:14

Ravi

1,1721 gold badge9 silver badges23 bronze badges

Collectives™ on Stack Overflow

Replace/Update multiple field values into a single value using sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related