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'

Answer 3

If you want to remove all asterisks then it's obvious:

SELECT REPLACE('Hello*', '*', '')

However, If you have more than one asterisk at the end and multiple throughout, but are only interested in trimming the trailing ones, then I'd use this:

DECLARE @String VarChar(50) = '**H*i****'
SELECT LEFT(@String, LEN(REPLACE(@String, '*', ' ')))              --Returns: **H*i

I updated this answer to include show how to remove leading characters:

SELECT RIGHT(@String, LEN(REPLACE(REVERSE(@String), '*', ' ')))    --Returns: H*i****

LEN() has a "feature" (that looks a lot like a bug) where it does not count trailing spaces.

Answer 4

LEFT('BOB*', LEN('BOB*')-1)

should do it.

Answer 5

If you only want to remove a single '*' character from the value when the value ends with a '*', a simple CASE expression will do that for you:

SELECT CASE WHEN RIGHT(foo,1) = '*' THEN LEFT(foo,LEN(foo)-1) ELSE foo END AS foo
  FROM (SELECT 'BOB*' AS foo)

To remove all trailing '*' characters, then you'd need a more complex expression, making use of the REVERSE, PATINDEX, LEN and LEFT functions.

NOTE: Be careful with the REPLACE function, as that will replace all occurrences of the specified character within the string, not just the trailing ones.

Answer 6

If you wanted behavior similar to how RTRIM handles spaces i.e. that "B*O*B**" would turn into "B*O*B" without losing the embedded ones then something like -

REVERSE(SUBSTRING(REVERSE('B*O*B**'), PATINDEX('%[^*]%',REVERSE('B*O*B**')), LEN('B*O*B**') - PATINDEX('%[^*]%', REVERSE('B*O*B**')) + 1))

Should do it.

Answer 7

How about.. (in this case to trim off trailing comma or period)

For a variable:

-- Trim commas and full stops from end of City
WHILE RIGHT(@CITY, 1) IN (',', '.'))    
    SET @CITY = LEFT(@CITY, LEN(@CITY)-1)  

For table values:

-- Trim commas and full stops from end of City
WHILE EXISTS (SELECT 1 FROM [sap_out_address] WHERE RIGHT([CITY], 1) IN (',', '.'))     
    UPDATE [sap_out_address]    
    SET [CITY] = LEFT([CITY], LEN([CITY])-1)  
    WHERE RIGHT([CITY], 1) IN (',', '.') 

Answer 8

An other approach ONLY if you want to remove leading and trailing characters is the use of TRIM function. By default removes white spaces but have te avility of remove other characters if you specify its.

SELECT TRIM('=' FROM '=SPECIALS=') AS Result;

Result  
--------
SPECIALS

Unfortunately LTRIM and RTRIM does not work in the same way and only removes white spaces instead of specified characters like TRIM does if you specify its.

Reference and more examples: https://database.guide/how-to-remove-leading-and-trailing-characters-in-sql-server/

Answer 9

I really like Teejay's answer, and almost stopped there. It's clever, but I got the "almost too clever" feeling, as, somehow, your string at some point will actually have a ~ (or whatever) in it on purpose. So that's not defensive enough for me to put into production.

I like Chris' too, but the PATINDEX call seems like overkill.

Though it's probably a micro-optimization, here's one without PATINDEX:

CREATE FUNCTION dbo.TRIMMIT(@stringToTrim NVARCHAR(MAX), @charToTrim NCHAR(1))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @retVal NVARCHAR(MAX)

    SET @retVal = @stringToTrim

    WHILE 1 = charindex(@charToTrim, reverse(@retVal))
        SET @retVal = SUBSTRING(@retVal,0,LEN(@retVal))

    WHILE 1 = charindex(@charToTrim, @retVal)
        SET @retVal = SUBSTRING(@retVal,2,LEN(@retVal))

    RETURN @retVal
END

--select dbo.TRIMMIT('\\trim\asdfds\\\', '\')
--trim\asdfds

Returning a MAX nvarchar bugs me a little, but that's the most flexible way to do this..

Answer 10

I've used a similar approach to some of the above answers of using pattern matching and reversing the string to find the first non-trimmable character, then cutting that off. The difference is this version does less work than those above, so should be a little more efficient.

• This creates RTRIM functionality for any specified character.
• It includes an additional step set @charToFind = case... to escape the chosen character.
• There is currently an issue if @charToReplace is a right crotchet (]) as there appears to be no way to escape this.

.

declare @stringToSearch nvarchar(max) = '****this is****a  ** demo*****'
, @charToFind nvarchar(5) = '*' 

--escape @charToFind so it doesn't break our pattern matching
set @charToFind = case @charToFind 
    when ']' then '[]]' --*this does not work / can't find any info on escaping right crotchet*
    when '^' then '\^'
    --when '%' then '%' --doesn't require escaping in this context
    --when '[' then '[' --doesn't require escaping in this context
    --when '_' then '_' --doesn't require escaping in this context
    else @charToFind
end

select @stringToSearch
, left
(
    @stringToSearch
    ,1 
    + len(@stringToSearch)
    - patindex('%[^' + @charToFind  + ']%',reverse(@stringToSearch))
)

Answer 11

SqlServer2017 has a new way to do it: https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-2017

SELECT TRIM('0' FROM '00001900'); -> 19

SELECT TRIM( '.,! ' FROM '# test .'); -> # test

SELECT TRIM('*' FROM 'BOB*'); --> BOB

Unfortunately, RTRIM does not support trimming a specific character.

Answer 12

    SELECT REPLACE('BOB*', '*', '')  
    SELECT REPLACE('B*OB*', '*', '') 
-------------------------------------
     Result : BOB
-------------------------------------

this will replace all asterisk* from the text

Answer 13

RRIM() LTRIM() only remove spaces try http://msdn.microsoft.com/en-us/library/ms186862.aspx

Basically just replace the * with empty space

REPLACE('TextWithCharacterToReplace','CharacterToReplace','CharacterToReplaceWith')

So you want

REPLACE ('BOB*','*','')

Answer 14

Trim with many cases

--id = 100 101 102 103 104 105 106 107 108 109 110 111
select right(id,2)+1  from ordertbl -- 1 2 3 4 5 6 7 8 9 10 11  -- last     two positions are taken

select LEFT('BOB', LEN('BOB')-1) -- BO

select LEFT('BOB*',1) --B
select LEFT('BOB*',2) --BO

Answer 15

Solution for one char parameter:

rtrim('0000100','0') -> select left('0000100',len(rtrim(replace('0000100','0',' '))))

ltrim('0000100','0') -> select right('0000100',len(replace(ltrim(replace('0000100','0',' ')),' ','.')))

Answer 16

@teejay solution is great. But the code below can be more understandable:

declare @X nvarchar(max)='BOB *'

set @X=replace(@X,' ','^')

set @X=replace(@X,'*',' ')

set @X= ltrim(rtrim(@X))

set @X=replace(@X,'^',' ')

Answer 17

Here's a function I used in the past. Note that while you can make it more general purpose by having extra parameters like the character(s) you wish to remove and what you will be replacing the space character(s) with, this greatly increases execution time. Here, I used a pipe to replace spaces AFTER pre-trimming the input. Change varchar to nvarchar if required.

CREATE FUNCTION [dbo].[TrimColons] 
    (
        @strToTrim varchar(500)
    )
    RETURNS varchar(500)
    AS
    BEGIN
        RETURN REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|'),':',' '))),' ',':'),'|',' ')
    
        /*
        Here's a breakdown of this fancy, schmancy, trimmer
    
        LTRIM(RTRIM(@strToTrim))  trims leading & trailing spaces first
        REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|')  replaces inside spaces with pipe char
        REPLACE(REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|'),':',' ') replaces demarc character, the colon, with spaces
        LTRIM(RTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|'),':',' ')))  trims the leading & trailing  converted-to-space demarc char (colon) 
        REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|'),':',' '))),' ',':') replaces the inner space characters back to demar char (colon)
        REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(LTRIM(RTRIM(@strToTrim)),' ','|'),':',' '))),' ',':'),'|',' ') replaces the pipe characters back to original space characters
    
        */
    END

Answer 18

DECLARE @String VarChar(50) = '**H*i****', @String2 VarChar(50)

--Assign to new variable @String2
;WITH X AS (
SELECT LEFT(@String, LEN(REPLACE(@String, '*', ' '))) [V1]
)
SELECT TOP 1 @String2 = RIGHT(V1, LEN(REPLACE(REVERSE(V1), '*', ' '))) FROM X

SELECT @String [@String], @String2 [@String2]

--See the intermediate values, v0 original, v1 triming end, and v2 trim the v1 leading
;WITH X AS (
SELECT @String V0,  LEFT(@String, LEN(REPLACE(@String, '*', ' '))) [V1]
)
SELECT [V0], [V1], RIGHT([V1], LEN(REPLACE(REVERSE([V1]), '*', ' '))) [v2] FROM X

Answer 19

Try this:

Original

select replace('BOB*','*','')

Fixed to be an exact replacement

select replace('BOB*','BOB*','BOB')