SQL Server TRIM 字符

SQL Server TRIM character

提问by Nate Pet

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

我有以下字符串：'BOB*'，如何修剪 * 使其显示为 'BOB'

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

我尝试了 RTRIM('BOB*','*') 但不起作用，因为只需要 1 个参数。

采纳答案by paulmorriss

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

should do it.

应该这样做。

回答by Teejay

Another pretty good way to implement Oracle's TRIM char FROM stringin MS SQL Serveris the following:

TRIM char FROM string在MS SQL Server 中实现 Oracle 的另一种很好的方法如下：

• 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+ RTrimthe obtained string
• You replace back all spaces with the trimmed character *
• You replace back all never-used characters with a space

• 首先，您需要确定一个永远不会在您的字符串中使用的字符，例如 ~
• 您用该字符替换所有空格
• 您用*空格替换要修剪的字符
• 你LTrim+RTrim得到的字符串
• 您用修剪过的字符替换回所有空格 *
• 您用空格替换回所有从未使用过的字符

For example:

例如：

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

回答by Chris Rodriguez

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'

回答by MikeTeeVee

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.

LEN() 有一个“功能”（看起来很像一个错误），它不计算尾随空格。

回答by George

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 -

如果您想要类似于 RTRIM 如何处理空格的行为，即“B*O*B**”将变成“B*O*B”而不会丢失嵌入的那些，那么类似于 -

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

Should do it.

应该做。

回答by spencer7593

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:

如果您只想'*'在值以 a 结尾时从值中删除单个字符'*'，一个简单的 CASE 表达式将为您完成：

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, LENand LEFTfunctions.

要删除所有尾随'*'字符，那么你需要一个更复杂的表达式，利用的REVERSE，PATINDEX，LEN和LEFT功能。

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

注意：使用 REPLACE 函数时要小心，因为它会替换字符串中所有出现的指定字符，而不仅仅是尾随的字符。

回答by Simon L

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

回答by Purplegoldfish

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

RRIM() LTRIM() 只删除空格试试http://msdn.microsoft.com/en-us/library/ms186862.aspx

Basically just replace the * with empty space

基本上只需用空格替换 *

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

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

So you want

所以你要

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

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

回答by ruffin

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.

我真的很喜欢Teejay 的回答，几乎停在那里。这很聪明，但我有一种“几乎太聪明了”的感觉，因为不知何故，你的字符串在某个时候实际上会~故意有一个（或其他什么）。所以这不足以让我投入生产。

I like Chris' too, but the PATINDEXcall seems like overkill.

我也喜欢Chris 的，但这个PATINDEX电话似乎有点矫枉过正。

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

虽然这可能是一个微优化，但这里有一个没有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 MAXnvarchar bugs me a little, but that's the most flexible way to do this..

返回MAXnvarchar 有点让我烦恼，但这是最灵活的方式来做到这一点..

回答by JohnLBevan

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 RTRIMfunctionality for any specified character.
• It includes an additional step set @charToFind = case...to escape the chosen character.
• There is currently an issue if @charToReplaceis a right crotchet (]) as there appears to be no way to escape this.

• 这RTRIM为任何指定的字符创建功能。
• 它包括一个额外的步骤set @charToFind = case...来转义所选字符。
• 当前存在一个问题，如果@charToReplace是正确的四角 ( ])，因为似乎无法避免这一点。

.

.

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