SQL Server TRIM character

Question

I have the following string: 'BOB*', how do I trim the * so it shows up as 'BOB'

I tried the RTRIM('BOB*','*') but does not work as says needs only 1 parameter.

Answer 1

Another pretty good way to implement Oracle's TRIM char FROM string in MS SQL Server is the following:

• First, you need to identify a char that will never be used in your string, for example ~
• You replace all spaces with that character
• You replace the character * you want to trim with a space
• You LTrim + RTrim the obtained string
• You replace back all spaces with the trimmed character *
• You replace back all never-used characters with a space

For example:

REPLACE(REPLACE(LTrim(RTrim(REPLACE(REPLACE(string,' ','~'),'*',' '))),' ','*'),'~',' ')

Answer 2

CREATE FUNCTION dbo.TrimCharacter
(
    @Value NVARCHAR(4000),
    @CharacterToTrim NVARCHAR(1)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    SET @Value = LTRIM(RTRIM(@Value))
    SET @Value = REVERSE(SUBSTRING(@Value, PATINDEX('%[^'+@CharacterToTrim+']%', @Value), LEN(@Value)))
    SET @Value = REVERSE(SUBSTRING(@Value, PATINDEX('%[^'+@CharacterToTrim+']%', @Value), LEN(@Value)))
    RETURN @Value
END
GO
--- Example
----- SELECT dbo.TrimCharacter('***BOB*********', '*')
----- returns 'BOB'