Removing prefix and suffix from a column

  • 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)?



  • 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.

  • 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;

    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)';
  • 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. 

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

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

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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