0

enter image description here

Some of my data has - Automated two or more times. I need to replace the text so it only appears once

I first tried to get the value to display by using this failed query

SELECT TRIM('- Automated') As TrimmedString FROM 
    (SELECT Comments FROM TableName WHERE Comments LIKE '%- Automated')

https://www.w3schools.com/sql/func_sqlserver_trim.asp

0
1

(In general, all these string-manipulation functions only operate on their arguments; if a column name is not one of the arguments, you are not manipulating the column!)

TRIM() is for trimming given characters in whatever order and quantity they may occur; if you want to trim given characters only if they occur as particular strings, TRIM() is the wrong tool.

There is no STRIP() function in the list of SQL Server Functions at the link you provided, but REPLACE() is there, and replacing X with an empty string is same as stripping X; so, try

SELECT REPLACE(Comments , '- Automated' , '') AS TrimmedString
FROM TableName
WHERE Comments LIKE '%- Automated'
1

Introduction:

With the question as asked, there are two possibilities (all the code below is available on the fiddle here):

Create your table:

CREATE TABLE test 
(
  x INT NOT NULL PRIMARY KEY,
  y VARCHAR (255) NOT NULL
);

Populate it with some test data:

INSERT INTO test VALUES 
(1, 'test'),
(2, ' - test - test'),
(3, 'test'),
(4, ' - test - test'),
(5, 'test'),
(6, 'test - test'),
(7, ' - test test'),
(8, ' xxxx - test -test'),
(9, 'other stuff');

Shotgun approach:

SELECT 
  CASE
    WHEN CHARINDEX('test', y, 1) = 0 THEN y
    ELSE 'test'
  END AS new_y
FROM test;

Result:

new_y
test
test
test
test
test
test
test
test
other stuff

What this does is

  • simply test (i.e. CASE) if the string contains your target word (in this case, I've used test)

  • if not - i.e. CHARINDEX() = 0, then just output the string which may contain anything but the word test -- **OR**

  • if it does not = 0 (i.e. test is there), then just output the word test - stripping anything else which may be there - hyphens, other words &c. before or after the first occurrence of the test word!

More subtle approach:

SELECT 
  CASE
    WHEN CHARINDEX('test', y, 1) = 0 THEN y
    ELSE SUBSTRING(y, 1, CHARINDEX('test', y, 1) + 3) -- 3 + 1 = length of test!
  END AS new_y
FROM test;

Result:

new_y
test
 - test 
test
 - test 
test
test 
 - test 
 xxxx - test 
other stuff

So, anything in front of the first occurrence of the word test will be kept using the SUBSTRING() function and adding the length of test (i.e. 4) to the first occurrence, and truncating the string at the end of the first occurrence of test and no further.

The update

Apart from realising that there was a small flaw in my initial logic - it should be 3 instead of 4 in the SQL - 3 + 1 = the length of test - the first character is included anyway, I realised that I hadn't done the UPDATE - it can be done as follows:

UPDATE
    t
SET
    t.y = tab.new_y
FROM
    test AS t
INNER JOIN 
(
  SELECT 
    x,
    CASE
      WHEN CHARINDEX('test', y, 1) = 0 THEN y
      ELSE SUBSTRING(y, 1, CHARINDEX('test', y, 1) + 3) -- 3 + 1 = length of test!
    END AS new_y
  FROM test
) AS tab
ON t.x = tab.x;

The fiddle shows: 9 rows affected.

Then, to check: SELECT * FROM test - result:

x   y
1   test
2    - test
3   test
4    - test
5   test
6   test
7    - test
8    xxxx - test
9   other stuff

i.e. our stripped table. The SQL Server UPDATE proved tricky - it's not my go-to server - I got help from here and here. Revised fiddle is at the link above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.