sql split and replace string in table

Question

I have data in table as below and trying to use Replace to do it, but I am ending up with either double semicolon ';;; or semicolon at end after using replace. Can I use split and replace to achieve this or any other ways??

    Test attempts;My attempts using operation;Additions, deletions, and modifications 
    Test attempts;My attempts using operation
    Test attempts;access to information;attempts using nex;Additions, deletions, and modifications
    Test attempts;Changes to programs

Replace 'My attempts using operation' with emptystring AND Replace 'attempts using nex' with 'attempts using imex' in above data column

    Result should be:

    Test attempts;Additions, deletions, and modifications 
    Test attempts-- No semicolon should be there if it ends up at end with that after replace
    Test attempts;access to information;attempts using imex;Additions, deletions, and modifications

Test attempts;Changes to programs

I am unsure why the standard REPLACE function does not work for you. e.g. SELECT REPLACE(REPLACE(col, ';My attempts using operation', ''), 'attempts using nex;', 'attempts using imex;') FROM mytable; — ZLK
– ZLK, Commented Mar 30, 2017 at 2:01
For second record Test attempts;My attempts using operation when I use replace it leaves a ; at end how should I account that in the script, there are about 10,000 rows in this pattern. How can I check if it ends up with ; at end and replace it with blank?? — user3038399
– user3038399, Commented Mar 30, 2017 at 3:20
So it can look like Test attempts;My attempts using operation or Test attempts;My attempts using operation;? In that case just add another replace (e.g. SELECT REPLACE(REPLACE(REPLACE(col, ';My attempts using operation;', ''), ';My attempts using operation', ''), 'attempts using nex;', 'attempts using imex;') FROM mytable;. If you're looking for a general solution to removing the trailing semicolon, you'd require a left/substring with a case statement. — ZLK
– ZLK, Commented Mar 30, 2017 at 3:36

Ranjana Ghimire · Accepted Answer · 2017-03-30 05:49:03Z

1

As simple as it can get

declare @test table (content varchar(8000))
insert into @test values ('Test attempts;My attempts using operation;Additions, deletions, and modifications ')
insert into @test values ('Test attempts;My attempts using operation')
insert into @test values ('Test attempts;access to information;attempts using nex;Additions, deletions, and modifications')
insert into @test values ('Test attempts;Changes to programs')

select 
    case charindex(';My attempts using operation',content) when 0 then content 
        else (substring(content,0,charindex(';My attempts using operation',content))
                +substring(content,charindex(';My attempts using operation',content)+len(';My attempts using operation'),len(content)))
        end
        from @test

answered Mar 30, 2017 at 5:49

Ranjana Ghimire

1,8451 gold badge14 silver badges22 bronze badges

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

1 Comment

user3038399 Over a year ago

I like this solution but It would fail for this data scenario when replacement text is at beginning.insert into @test values ('My attempts using operation;Test attempts')

Alan Burstein · Accepted Answer · 2017-03-30 05:04:21Z

First the sample data

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(OriginalString varchar(1000));
INSERT #t
VALUES 
('Test attempts;My attempts using operation;Additions, deletions, and modifications'),
('Test attempts;My attempts using operation'),
('Test attempts;access to information;attempts using nex;Additions, deletions, and modifications'),
('Test attempts;Changes to programs');

How about this:

WITH prep(NewString) AS
(
  SELECT
    REPLACE(REPLACE(REPLACE(
      OriginalString, 'attempts using nex', 'attempts using imex'
    ), 'My attempts using operation', ''), ';;', ';')
  FROM #t
)
SELECT
  NewString = 
    CASE 
      WHEN NewString LIKE '%;' 
      THEN SUBSTRING(NewString, 1, LEN(NewString)-1) 
      ELSE NewString 
    END
FROM prep;

Gouri Shankar Aechoor · Accepted Answer · 2017-03-30 09:04:16Z

Hope this helps. As per your request, the string will be split on the ; and reconstructed without the excluded string

DECLARE @RemoveString VARCHAR(8000) = 'My attempts using operation'

;WITH cte_TestData (StringValue)
AS (
    SELECT 'Test attempts;My attempts using operation;Additions, deletions, and modifications '
    UNION ALL
    SELECT 'Test attempts;My attempts using operation'
    UNION ALL
    SELECT 'Test attempts;access to information;attempts using nex;Additions, deletions, and modifications'
    UNION ALL
    SELECT 'Test attempts;Changes to programs'
    UNION ALL
    SELECT 'Test attempts'
    ),
cte_Sequencer
AS (
    SELECT ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) AS Id,
        StringValue
    FROM cte_TestData
    ),
cte_XMLBuilder
AS (
    SELECT Id,
        StringValue,
        CAST('<M>' + REPLACE(StringValue, ';', '</M><M>') + '</M>' AS XML) AS XMLData
    FROM cte_Sequencer
    ),
StringSplitter
AS (
    SELECT C1.Id,
        ROW_NUMBER() OVER (
            PARTITION BY C1.Id ORDER BY (
                    SELECT NULL
                    )
            ) AS SeqId,
        C2.StringValue,
        SPLITS.ABC.value('.', 'varchar(MAX)') AS Data
    FROM cte_XMLBuilder C2
    INNER JOIN cte_XMLBuilder C1
        ON C2.StringValue = C1.StringValue
    CROSS APPLY C1.XMLData.nodes('/M') AS SPLITS(ABC)
    )
SELECT DISTINCT ID,
    StringValue,
    STUFF((
            SELECT ';' + b.Data AS [text()]
            FROM StringSplitter b
            WHERE b.Id = a.Id
                AND Data != @RemoveString
            ORDER BY id,
                SeqId
            FOR XML PATH('')
            ), 1, 1, '') AS EditedString
FROM StringSplitter a

Collectives™ on Stack Overflow

sql split and replace string in table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related