Home Forums SQL Server 2005 T-SQL (SS2K5) How to Delete Right 3 Characters from a string RE: How to Delete Right 3 Characters from a string

  • July 25, 2011 at 9:16 am

    #1357848

    Mad Myche (7/25/2011)

    Ninja's_RGR'us (7/24/2011)

    All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.

    As long as there are a minimum of 3 characters in the field

    Pick your poison, you always have to be careful with that type of manipulations. Here are the "fully correct" options.

    DECLARE @a TABLE (col VARCHAR(10))

    INSERT INTO @a (col) VALUES ('')

    INSERT INTO @a (col) VALUES (NULL)

    INSERT INTO @a (col) VALUES ('123')

    INSERT INTO @a (col) VALUES ('123123123')

    INSERT INTO @a (col) VALUES ('123456789')

    SELECT col

    , REPLACE(col, RIGHT(col, 3), '') AS [Replace = wrong output]

    , CASE WHEN col IS NOT NULL THEN ISNULL(STUFF(col, DATALENGTH(col) - 3 + 1, 3,''), '') ELSE NULL END AS Stuf

    , CASE WHEN DATALENGTH(col) >= 3 THEN LEFT(col, DATALENGTH(col) - 3) ELSE CASE WHEN col IS NULL THEN col ELSE '' END END AS Lft

    FROM @a