Remove specific sub strings from given string?

Question

I have string separated by semicolons. There are some sub-strings which contains specific character like pipe |.

How to remove those sub-string using SQL Server?

Example:

1,abc;1,abc|1,def;1,abc|2,def;1,abc|3,def;1,abc|4,def;1,abc|5,def;1,abc|6,def;2,abc;2,abc|7,def;2,abc|7,def|1,xyz;2,abc|8,def

Result should be - 1,abc;2,abc;....

your question, example and expected result don't really match well together. I'd rather expect a result '1,abc;2,abc;...' for the given example — bw_üezi, Feb 05 '16 at 07:56

score 0 · Answer 1 · answered Feb 05 '16 at 06:57

0

SELECT REPLACE('1,abc;1,abc|1,def;1,abc|2,def;1,abc|3,def;1,abc|4,def;1,abc|5,def;1,abc|6,def;2,abc;2,abc|7,def;2,abc|7,def|1,xyz;2,abc|8,def','|','')

answered Feb 05 '16 at 06:57

Raj

10,653
2
45
52

Hi Raj, as I mentioned, need to remove hole sub string having pipe '|'. – Mudit Dubey Feb 05 '16 at 07:02
If string like 1,abc;1,abc|1,def;1,abc|2,def;1,abc|3,def; then result shoult be - 1,2,... or 1,abc;2,def... – Mudit Dubey Feb 05 '16 at 07:03

score 0 · Answer 2 · edited May 23 '17 at 10:28

Create a function to split your string. Like the one in this post:

CREATE FUNCTION [dbo].[SDF_SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
            @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
        set @iStart = 2
        insert into @tParts
        values( null )
    end
    else 
        set @iStart = 1
    while 1=1
    begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
            set @iPos = len( @sString )+1
        if @iPos - @iStart > 0          
            insert into @tParts
            values  ( substring( @sString, @iStart, @iPos-@iStart ))
        else
            insert into @tParts
            values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
            break
    end
    RETURN

END

Then you'll have to remove the values from the returned table that contain the pipe '|' or you just select the values out of the returned table that don't contain any pipe '|'.

score 0 · Answer 3 · answered Feb 05 '16 at 08:34

0

If it was not for sql-server I would go with a regular expression to solve this problem.

This regular expression shows those substings to keep: ^[^|]+;|;[^|]+;|;[^|]+$
or this regular expression to cut those substrings to remove: ;[^;]+\|[^;]+

answered Feb 05 '16 at 08:34

bw_üezi

4,483
4
23
41

Remove specific sub strings from given string?

3 Answers3