CODEWITHSUNDEEP
sqlsql-server
user2528557
user2528557

Reputation: 1427

Remove unwanted text from column values

I dont know how to do this. On my table I have descriptions mixed with description code. I need to remove the code description, just want the description. The description is just the first part without the acronym (capital letters at the end). I use SQL Server 2012

Example:

ColumnDescription
Chemistry Q
education E
psychology P
Sociology SOC
Documentation DOC
communication COM
Political Science CP
Pharmacy and Toxicology FT
Engineering Education (General) ING-G

Upvotes: 0

Views: 1583

Answers (2)

gvee
gvee

Reputation: 17161

Based on your sample data I would guess that your problem could be simplified to:

Split the string at the last space character in the string

In which case:

DECLARE @your_table table (
   ColumnDescription varchar(100)
);

INSERT INTO @your_table (ColumnDescription)
  VALUES ('Chemistry Q')
       , ('education E')
       , ('psychology P')
       , ('Sociology SOC')
       , ('Documentation DOC')
       , ('communication COM')
       , ('Political Science CP')
       , ('Pharmacy and Toxicology FT')
       , ('Engineering Education (General) ING-G');

SELECT *
     , SubString(ColumnDescription, number_of_characters - last_space + 2, 100) As last_part
     , SubString(ColumnDescription, 0, number_of_characters - last_space + 2) As first_part
FROM   (
        SELECT ColumnDescription
             , Len(ColumnDescription) As number_of_characters
             , Reverse(ColumnDescription) As reversed
             , CharIndex(' ', Reverse(ColumnDescription)) As last_space
        FROM   @your_table
       ) As x;

Upvotes: 0

Martin
Martin

Reputation: 16433

If you are looking to simply strip the code that is at the end of each string, a way to do this is to identify the last space character in the string and then use SUBSTRING to extract everything before that character:

SELECT  SUBSTRING(ColumnDescription, 0, LEN(ColumnDescription) - CHARINDEX(' ', REVERSE(ColumnDescription)) + 1) AS ColumnDescription
  FROM  Table

Note that I do not know what your table is called, so I called it Table.

This effectively reverses the column text (using REVERSE), finds the first occurrence of a space character (using CHARINDEX) and then subtracts this from the length of the text (using LEN). Then a simple SUBSTRING is used to extract the left-most portion of the text, resulting in the output of:

ColumnDescription
-----------------
Chemistry
education
psychology
Sociology
Documentation
communication
Political Science
Pharmacy and Toxicology
Engineering Education (General)

Upvotes: 1

Related Questions