SQL删除两个字符之间的字符串

I have a string such as this:

我有一个这样的字符串:

`a|b^c|d|e^f|g`

and I want to maintain the pipe delimiting, but remove the carrot sub-delimiting, only retaining the first value of that sub-delimiter.

我想保持管道分隔,但删除胡萝卜子分隔,只保留该子分隔符的第一个值。

The output result would be:

输出结果将是:

`a|b|d|e|g`

Is there a way I can do this with a simple SQL function?

有没有办法用简单的SQL函数做到这一点?

3 个解决方案

#1

Another option, using CHARINDEX, REPLACE and SUBSTRING:

另一个选项,使用CHARINDEX,REPLACE和SUBSTRING:

DECLARE @OriginalString varchar(50) = 'a|b^c^d^e|f|g'

DECLARE @MyString varchar(50) = @OriginalString 

WHILE CHARINDEX('^', @MyString) > 0 
BEGIN
    SELECT @MyString = REPLACE(@MyString, 
                               SUBSTRING(@MyString, 
                                         CHARINDEX('^', @MyString), 
                                         CASE WHEN CHARINDEX('|', @MyString, CHARINDEX('^', @MyString)) > 0 THEN
                                            CHARINDEX('|', @MyString, CHARINDEX('^', @MyString)) - CHARINDEX('^', @MyString)
                                         ELSE
                                            LEN(@MyString)
                                         END
                                         )
                       , '')
END

SELECT @OriginalString As Original, @MyString As Final

Output:

Original              Final
a|b^c^d^e|f|g         a|b|f|g

#2

This expression will replace the first instance of caret up to the subsequent pipe (or end of string.) You can just run a loop until no more rows are updated or no more carets are found inside the function, etc.

此表达式将替换插入符号的第一个实例,直到后续管道(或字符串结尾)。您可以只运行循环,直到不再更新行或在函数内找不到更多插入符号等。

case
    when charindex('^', s) > 0
    then stuff(
             s,
             charindex('^', s),
             charindex('|', s + '|', charindex('^', s) + 1) - charindex('^', s),
             ''
         )
    else s
end

Here's a loop you can adapt for a function definition:

这是一个可以适应函数定义的循环:

declare @s varchar(30) = 'a|b^c^d|e|f^g|h^i';
declare @n int = charindex('^', @s);

while @n > 0
begin
    set @s = stuff(@s, @n, charindex('|', @s + '|', @n + 1) - @n, '');
    set @n = charindex('^', @s, @n + 1);
end
select @s;

A little bit of care needs to be taken for the trailing of the string where there won't be a final pipe separator. You can see I've handled that.

对于没有最终管道分隔器的字符串的尾随,需要稍加注意。你可以看到我已经处理过了。

#3

First split the values delimited by | then extract the first value and concatenate the result back to get the result

首先拆分由|分隔的值然后提取第一个值并将结果连接起来以获得结果

Try this

SELECT Cast(left(intr,len(intr)-1) AS VARCHAR(1000)) AS res
FROM   (SELECT LEFT(split_val, Isnull(NULLIF(Charindex('^', split_val), 0)-1, Len(split_val))) + '|'
        FROM   Udf_splitstring('a|bdd^c|d|e^f|g', '|')
        FOR xml path (''))a (intr)

Result : a|bdd|d|e|g

结果:a | bdd | d | e | g

Here is a article about split string functions Split strings the right way – or the next best way

这是一篇关于拆分字符串函数的文章以正确的方式拆分字符串 - 或者是下一个最佳方式

#1