Search code examples
sql-serverreplacetrim

Combining LTRIM and RTIM and REPLACE functions in MSSQL

I have a field in a table that has both leading and trailing characters that I would like to remove.

This Statement

SELECT FullName AS Category FROM dbo.inventory

produces this result in a table (partial result shown):

  • Parts - Belts:Z.360J8
  • Parts - Belts:Z.360J14
  • Parts - Belts:Z.36355-123
  • Parts - Plastics & Frame:Z.103196-001
  • Parts - Electrical Cables/Cords:Z.P037153674

I would like to remove the before the dash which would remove "Parts - " and everything trailing the colon ':' so I can be left with only the category and get a table like this:

  • Belts
  • Belts
  • Belts
  • Plastics & Frame
  • Electrical Cables/Cords

I have tried this and successfully removed the preceding "Parts -" but successfully combine the statement with removing everything after the colon ":"

SELECT LTRIM(RTRIM(REPLACE(FullName, 'Parts -', ''))) AS Category FROM dbo.inventory

Produces this result:

  • Belts:Z.360J8
  • Belts:Z.360J14
  • Belts:Z.36355-123
  • Plastics & Frame:Z.103196-001
  • Electrical Cables/Cords:Z.P037153674

Thanks for you help!

Solution

  • A method using LEFT & CHARINDEX:

    SELECT REPLACE(LEFT(FullName, CHARINDEX(':', FullName) - 1), 'Parts - ', '')

    CHARINDEX finds the index number of the colon character in the full string, -1 is included to remove it too. LEFT then truncates the string to that position. The REPLACE functions as it does in your original example, removing the 'Parts - ' portion (note I've included the space following the hyphen).

    LTRIM and RTRIM only remove leading/trailing spaces respectively, so they won't be useful here.

    Code to handle rows containing no colon (will return a blank string):

    SELECT REPLACE(REPLACE(LEFT(Fullname, CHARINDEX(':', Fullname)), 'Parts - ', ''), ':', '')