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.

Electronic

New answer to an old question...If you are using SQL Server 2017, the TRIM() function may be a solution for some of you. – Sexuality

Given how specific the accepted answer is, i suggest SELECT 'BOB' as a more performant alternative – Tallia

Answer 1

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

should do it.

Smallclothes

This will remove the last character from the string, whether or not that character is an asterisk. This works in the special case of strings that end with an asterisk, but doesn't do any check whether the last character should be removed or not. – Bronk

It doesn't trim more than one asterisk either. – Postlude

The original question didn't say "I only want to remove an asterisk if there's one there" or "I want to remove all asterisks no matter how many there are". I answered the question that was asked. – Smallclothes

it will remove last character from the value regardless the character is special or alpha or numeric, it is not a generic solution at all. If you do only for 'BOB' so the result will be 'BO' which is not a valid according to question. – Faroff

It isn't a generic solution, because it isn't a generic question. It's a very specific question about one string. – Smallclothes

LEFT('BOB*',3) – Bronk

That's an even less generic solution than mine - well done! – Smallclothes

Answer 2

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,' ','~'),'*',' '))),' ','*'),'~',' ')

Aston

This is great. I adapted a version of this to remove the preceeding '[' and ']' from strings, which was useful to detect if they were db object nouns (tables, sp's, etc) – Damsel

From SQL Server vNext you could use TRIM function like here https://mcmap.net/q/658522/-how-to-trim-string-using-predefined-special-characters-in-sql-server – Hanseatic

Answer 3

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'

Holpen

this answer needs some love – Disengagement

Answer 4

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.

Swithin

This way you'd be trimming any space at the end. Also you are relying on a bug that is likely to be corrected now or then... – Aston

@Aston There's no way that the behaviour of LEN will be changed now for backwards compatibility reasons. The ignoring trailing white space is how it is documented to work so won't be acknowledged as a bug. – Fipple

Answer 5

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.

Claptrap

Unfortunately this method cannot trim ] and ^ (see my question at stackoverflow.com/questions/15290737). I wrote two small functiona that implement LTRIM and RTRIM on MsSql server, see my self-answer in the same page linked above. – Aston

this works, but only right trims the value... what would be the optimal way to trim both sides? – Postlude

Answer 6

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.

Bronk

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 (',', '.') 

Chacon

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/

Chrono

There is new syntax which allows you to use the new TRIM functionality, but only on one side. For example TRIM(LEADING '=' FROM '=SPECIALS=') will do the equivalent of LTRIM, but with a user-specified trim character. See the docs. – Hepner

Answer 9

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.

Paleobotany

Answer 10

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*','*','')

Gerhardt

This will replace all the * in the string, not only the leading and trailing ones. – Pentheas

Answer 11

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..

Corinnacorinne

I like this answer, however it may return expected results for some cases. For example select dbo.TRIMMIT('\trim\asdfds ', '\') (There are multiple space characters after asdfds but SO trims them!) returns trim\asdfds , because function LEN returns the number of characters of the specified string expression, excluding trailing blanks. You may need to use datalength instead. – Cecilius

Answer 12

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))
)

Avian

Post to investigate my ] issue here: #35780165 – Avian

Answer 13

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

this will replace all asterisk* from the text

Dric

Answer 14

This is now supported in SQL Server 2022 and Azure SQLDB

https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver16#syntax

select RTRIM('BOB*','*');

Screenshot from SSMS & SQL Server2022

Tuddor

Answer 15

Try this:

Original

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

Fixed to be an exact replacement

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

Joanjoana

This will replace all the * in the string, not only the leading and trailing ones. – Pentheas

Good point. I have corrected the sample. However I must point out that the string being passed is a literal, so there is only one star in it anyway. – Joanjoana

Answer 16

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

Jonahjonas

Answer 17

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',' ')),' ','.')))

Cyclopedia

Answer 18

@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,'^',' ')

Chet

Answer 19

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

Olympic

Answer 20

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

Bushey