SQL Server: replace multiple occurrences of a word with one

Question

I have a SQL Server table with a column Event which contains values like this:

[Event]
----------------------------------------
Payment stopped, Claim, Claim, Claim
Claim
Claim, Claim, Claim
Claim, Claim, Payment stopped, Case Closed

Now the user wants to view this column which has the summary count of the word 'Claim', so that the below column would look like

[Event]
-------------------------------------
Payment stopped, 3 Claims
Claim
3 Claims
2 Claims, Payment stopped, Case Closed

The word 'Claim' could present up to 400 times, they don't mind keeping the count in the starting of the value (3 Claims, Payment stopped), or end of the value (Payment stopped, 3 Claims). I have an udf that could get the count of this word, but removing the commas, spaces seems really difficult.

Is there a way to do this (with or without udf)? I am using SQL Server 2008.


Show source
| sql-server   | database   | sql-server-2008   2017-01-03 10:01 4 Answers

Answers to SQL Server: replace multiple occurrences of a word with one ( 4 )

  1. 2017-01-03 11:01

    First you need to split the items by id using split function and get the count of each item. After that we need to conctenate the result. Below is the query with sample data.

    declare @myevent table(id int identity,name varchar(max));
    
    insert into @myevent select 'Payment stopped, Claim, Claim, Claim';
    insert into @myevent select 'Claim';
    insert into @myevent select 'Claim, Claim, Claim';
    insert into @myevent select 'Claim, Claim, Payment stopped, Case Closed';
    
    with cte as(
        select id,cast(count(item) over(partition by id,ltrim(rtrim(item))) as varchar(5)) + ' ' + ltrim(rtrim(item)) Item
        from @myevent
            cross apply dbo.Split(name,',')
    )
    ,cte1 as(
        select distinct id,item
        from cte
    )
    select distinct id
            ,substring((select ','+item
                        from cte1 c1
                        where c1.id = c2.id
                        order by id
                        for xml path('')
                        )
                        , 2, 1000
                    ) [result]
    from cte1 c2
    

    Below is the Split Function

    CREATE FUNCTION [dbo].[Split] (
          @InputString VARCHAR(8000),
          @Delimiter VARCHAR(50)
    )
    
    RETURNS @Items TABLE (
          Item VARCHAR(8000)
    )
    
    AS
    BEGIN
          IF @Delimiter = ' '
          BEGIN
                SET @Delimiter = ','
                SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
          END
    
          IF (@Delimiter IS NULL OR @Delimiter = '')
                SET @Delimiter = ','
    
          DECLARE @Item VARCHAR(8000)
          DECLARE @ItemList VARCHAR(8000)
          DECLARE @DelimIndex INT
    
          SET @ItemList = @InputString
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
          WHILE (@DelimIndex != 0)
          BEGIN
                SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
                INSERT INTO @Items VALUES (@Item)
    
                -- Set @ItemList = @ItemList minus one less item
                SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
                SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
          END -- End WHILE
    
          IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
          BEGIN
                SET @Item = @ItemList
                INSERT INTO @Items VALUES (@Item)
          END
    
          -- No delimiters were encountered in @InputString, so just return @InputString
          ELSE INSERT INTO @Items VALUES (@InputString)
    
          RETURN
    
    END -- End Function
    

    output

    id  result
    1   1 Payment stopped,3 Claim
    2   1 Claim
    3   3 Claim
    4   1 Case Closed,1 Payment stopped,2 Claim,2 Claim
    
  2. 2017-01-03 11:01

    Try the below script

    DECLARE @V_WORD NVARCHAR(10)    =   'Claim'
    
    DECLARE @TABLE  TABLE
    (   [Event] NVARCHAR(MAX))
    
    INSERT INTO @TABLE
    VALUES('Payment stopped, Claim, Claim, Claim'),('Claim'),('Claim, Claim, Claim'),('Claim, Claim, Payment stopped, Case Closed')
    
    ;WITH CTE
    AS  (
            SELECT  [Event]
                    ,CHARINDEX(@V_WORD,[Event],0)   [stpos]
                    ,(LEN([Event]) - LEN(REPLACE([Event],@V_WORD,''))) / LEN(@V_WORD)   AS  [ECount]
            FROM    @TABLE
    )
    
    SELECT  REPLACE([Event]
                    ,SUBSTRING([Event],[stpos],([ECount] * LEN(@V_WORD)) + (([ECount]-1) * 2))
                    ,CAST([ECount] AS NVARCHAR) +' Claim' +
                        (CASE WHEN [ECount] > 1 THEN 's' ELSE '' END) 
                    )   [Result]
    FROM    CTE
    

    Result:

    Payment stopped, 3 Claims
    1 Claim
    3 Claims
    2 Claims, Payment stopped, Case Closed
    
  3. 2017-01-03 12:01

    I agree with the other commenters that you should really look at your import processes and data schema to hold all the different events as individual table rows.

    If this is not possible however, you can do the required manipulation relatively simply, without the use of ctes or additional functions. Be mindful that this will not work for anything other than the word Claim, as that is all that has been asked for in your question:

    declare @Event table(Event nvarchar(500));
    insert into @Event values
     ('Payment stopped, Claim, Claim, Claim')
    ,('Claim')
    ,('Claim, Claim, Claim')
    ,('Claim, Claim, Payment stopped, Case Closed')
    ,('Payment stopped, Case Closed');
    
    select Event
            ,case (len(Event) - len(replace(Event,'Claim','')))/5
                        when 0 then ''
                        when 1 then '1 Claim' + case when len(replace(Event,'Claim','')) > 0 then ', ' else '' end
                        else cast((len(Event) - len(replace(Event,'Claim','')))/5 as nvarchar(5)) + ' Claims' + case when len(replace(Event,'Claim','')) > 0 then ', ' else '' end
                     end
                + case when len(replace(Event,'Claim','')) > 0
                        then replace(replace(replace(Event,', Claim',''),'Claim, ',''),'Claim','')
                        else ''
                        end as Result
    from @Event;
    

    Output:

    Event                                       | Result
    ````````````````````````````````````````````|````````````````````````````````````````
    Payment stopped, Claim, Claim, Claim        | 3 Claims, Payment stopped
    Claim                                       | 1 Claim
    Claim, Claim, Claim                         | 3 Claims, 
    Claim, Claim, Payment stopped, Case Closed  | 2 Claims, Payment stopped, Case Closed
    Payment stopped, Case Closed                | Payment stopped, Case Closed
    
  4. 2017-01-03 13:01

    One more way is scalar function for doing this.

    Schema from your Question.

    CREATE TABLE #Event (EVENT_LIST VARCHAR(MAX))
    
    INSERT INTO #Event
    SELECT 'Payment stopped, Claim, Claim, Claim'
    UNION ALL
    SELECT 'Claim'
    UNION ALL
    SELECT 'Claim, Claim, Claim'
    UNION ALL
    SELECT 'Claim, Claim, Payment stopped, Case Closed'
    

    You need to do count of each word for every record. So need to create a Function which will execute per row once and gives result.

    Function with Logic:

    CREATE FUNCTION [dbo].FN_REPEAT_COUNT(@VAR VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    
     SET @VAR=@VAR+','
    ;WITH CTE AS  --Recursive CTE for calculating ',' indexes
    (
        SELECT 1 INDX_FRM 
        , LEN(@VAR) LEN_VAR
        , CHARINDEX(',',@VAR)+1 AS INDX_TO
    
        UNION ALL
    
        SELECT CAST(INDX_TO+1 AS INT)
        , LEN_VAR
        , CHARINDEX(',',SUBSTRING(@VAR,INDX_TO+1,LEN_VAR))+INDX_TO+1 
    
        FROM CTE WHERE INDX_TO<LEN_VAR
    )
    ,CTE2 AS(    --cte to generate records based on ',' index
    SELECT  SUBSTRING(@VAR,INDX_FRM,INDX_TO-INDX_FRM-1 ) AS LIST FROM CTE
    )
    ,CTE3 AS (  --cte for count of word Claim and making them back to column
    SELECT (
            SELECT CAST(COUNT(CASE 
                            WHEN LIST = 'Claim'
                                THEN 1
                            ELSE NULL
                            END) AS VARCHAR(2)) + ' ' + LIST + ','
            FROM CTE2
            GROUP BY LIST
            FOR XML PATH('')
            ) COUNTED
    
    )  
    --Removing 0 and replacing ',' with Empty String
    SELECT @VAR = REPLACE( SUBSTRING(COUNTED,1,LEN(COUNTED)-1),'0','') FROM CTE3
    RETURN @VAR
    END
    

    And now just call the Scalar function on your column like below

    SELECT *, dbo.FN_REPEAT_COUNT(EVENT_LIST) AS FN_RES FROM #Event
    

    And the Output is

    ╔════════════════════════════════════════════╦═══════════════════════════════════════╗
    ║                 EVENT_LIST                 ║                FN_RES                 ║
    ╠════════════════════════════════════════════╬═══════════════════════════════════════╣
    ║ Payment stopped, Claim, Claim, Claim       ║ 3 Claim, Payment stopped              ║
    ║ Claim                                      ║ 1 Claim                               ║
    ║ Claim, Claim, Claim                        ║ 3 Claim                               ║
    ║ Claim, Claim, Payment stopped, Case Closed ║  Case Closed,2 Claim, Payment stopped ║
    ╚════════════════════════════════════════════╩═══════════════════════════════════════╝
    

Leave a reply to - SQL Server: replace multiple occurrences of a word with one

◀ Go back