sql server - Combining LTRIM and RTIM and REPLACE functions in MSSQL -

i have field in table has both leading , trailing characters remove.

this statement

select fullname category dbo.inventory

produces result in 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 remove before dash remove "parts - " , trailing colon ':' can left category , table this:

• belts
• belts
• belts
• plastics & frame
• electrical cables/cords

i have tried , removed preceding "parts -" combine statement removing after colon ":"

select ltrim(rtrim(replace(fullname, 'parts -', ''))) category dbo.inventory

produces result:

• belts:z.360j8
• belts:z.360j14
• belts:z.36355-123
• plastics & frame:z.103196-001
• electrical cables/cords:z.p037153674

thanks help!

a method using left & charindex:

select replace(left(fullname, charindex(':', fullname) - 1), 'parts - ', '') 

charindex finds index number of colon character in full string, -1 included remove too. left truncates string position. replace functions in original example, removing 'parts - ' portion (note i've included space following hyphen).

ltrim , rtrim remove leading/trailing spaces respectively, won't useful here.

code handle rows containing no colon (will return blank string):

select replace(replace(left(fullname, charindex(':', fullname)), 'parts - ', ''), ':', '')