SQL Remove string between two characters

Question

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?

which sql you want to use? Anyway, you can simply replace the ^ and the following char with empty space, since you always take the first. — Mihai-Daniel Virna
– Mihai-Daniel Virna, Commented Mar 15, 2016 at 16:35
Specify your RDBMS (Firebird/Teradata/MariaDB/...). Does your data contain data like a|ab^cd^ef|e^da? — Lukasz Szozda
– Lukasz Szozda, Commented Mar 15, 2016 at 16:35
I am using sql server 2005, and yes it can contain values such as a|b^c^d^e|f|g, and I want to output a|b|f|g. Basically keep anything before the ^, and get rid of everything till the next pipe. — kunkel1
– kunkel1, Commented Mar 15, 2016 at 16:37

Zohar Peled · Accepted Answer · 2016-03-15 17:17:02Z

3

Another option, using CHARINDEX, REPLACE and 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

edited Mar 15, 2016 at 17:17

answered Mar 15, 2016 at 16:58

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

shawnt00 Over a year ago

@kunkel1 Hate to tell you this is an infinite loop when there's no pipe after the final caret.

Zohar Peled Over a year ago

Edited my answer to handle the case when the string is not closed with a pipe (|)

Zohar Peled Over a year ago

@shawnt00 After reading your answer I tested and edited mine. I have to admit your solution is probably better, though. upvoted your answer.

Solomon Rutzky Over a year ago

@shawnt00 "I suspect that reputation often sways OPs": uh, yeah ;-). Well, voters in general. It's one of the main down-sides to listing rep on anything but a user's profile page. It makes it too easy to gain implicit trust such that sometimes voters don't even bother checking the answer before up-voting (though I am not implying that this answer should not have gotten any votes, I am just pointing out a behavior that I have noticed).

kunkel1 Over a year ago

I appreciate all of the help. I picked the top answer based on which one I could plug in and make work in my application with my limited SQL knowledge. I couldn't get the other two to work (most likely my fault). Thanks for following up with the modification. My test values did not have this situation, but it could have been bad to have left it, so I made the correction and everything looks great. :)

|

shawnt00 · Accepted Answer · 2016-03-15 17:11:21Z

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.

Pரதீப் · Accepted Answer · 2016-03-15 18:21:36Z

2

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

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

edited Mar 15, 2016 at 18:21

answered Mar 15, 2016 at 16:52

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

4 Comments

shawnt00 Over a year ago

You're assuming the values are going to be single characters.

Zohar Peled Over a year ago

what if the original string is |xb^dfg|b|f^df|? this answer will return |x|b|f|, instead of |xb|b|f|...

Pரதீப் Over a year ago

@shawnt00 - Yeah becoz of sample data, check now updated

Pரதீப் Over a year ago

@ZoharPeled - Updated my answer

Collectives™ on Stack Overflow

SQL Remove string between two characters

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related