Removing prefix and suffix from a column

Question

Removing prefix and suffix from a column

Raxso1

SSC Veteran

Points: 270
More actions
May 12, 2021 at 8:56 pm

#3882754

Hi
I have a table with a could that has over 3000 rows for employees name, in some of the rows there is names 'ZZZ Atkin (left)'
How can I remove prefix starting with ZZZ and suffix with (left)?
Regards
Raxso

May 13, 2021 at 7:15 am

This was removed by the editor as SPAM
May 14, 2021 at 1:50 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24684 More actions · Answer 1

If the prefix and suffix you are wanting to remove are static known values, then probably the easiest way to remove them is with REPLACE:

REPLACE(REPLACE(column,'ZZZ ',''),' (left)','')

I mean you could also do it with LEFT, RIGHT and LEN... something along the lines of:

RIGHT(LEFT(column, LEN(column) - 7),LEN(column)-11)

Now, side effects of the above 2 methods are

METHOD 1

if your string in the middle that you want contains 'ZZZ " or ' (left)' it would get stripped out.

METHOD 2

you need to toss a CASE onto the front of that to see if it starts with ZZZ and ends with (left) or you may get errors.

Depending on your source data would determine which method I recommend. Method 2 I anticipate to be a slower performance solution but less likely to remove data you want to keep. Method 1 should perform better and since it is a column of employee names, is unlikely to have "ZZZ" or "(left)" in a name so SHOULD be safe.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

pietlinden SSC Guru Points: 63425 More actions · Answer 2

I did it a different way than Brian did... the SELECT queries are always nice to make sure you're affecting only the records you intend to... (better safe than sorry!)

use tempdb;
go

CREATE TABLE Employee (
FullName VARCHAR(30) NOT NULL
);

INSERT INTO Employee VALUES ('ZZZZ Atkin (left)'),('ZZZZ Franklin (left)'),('Fred Jones'),('Ziggy Stardust');

SELECT *
FROM Employee
WHERE FullName LIKE 'ZZZZ %';

-- Remove leading 'ZZZZ '
UPDATE Employee
SET FullName = RIGHT(FullName,LEN(FullName)-5)
WHERE FullName LIKE 'ZZZZ %';

SELECT *
FROM Employee
WHERE RIGHT(FullName,6) = '(left)';

-- remove trailing '(left)'
UPDATE Employee
SET FullName = LEFT(FullName, LEN(FullName) - 6)
WHERE RIGHT(FullName,6) = '(left)';

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 3

After doing this for about 50 years, I found the best way to handle names is to get a specialized piece of software for them and use it to scrub your data. It just gets too complicated for you to devote time to it. Work on the problem you're hired to solve.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 4

UPDATE yt
SET name = LTRIM(RTRIM(name3))
FROM your_table yt
CROSS APPLY (
    SELECT CASE WHEN yt.name LIKE 'ZZZZ%' THEN STUFF(yt.name, 1, 4, '') ELSE yt.name END AS name2
) AS after_remove_prefix
CROSS APPLY (
    SELECT CASE WHEN name2 LIKE '%(left)' THEN LEFT(name2, LEN(name2) - 6) ELSE name2 END AS name3
) AS after_remove_suffix
WHERE yt.name LIKE 'ZZZZ%' OR yt.name LIKE '%(left)'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.