Sql Server Remove Some Specific Characters From String

SQL Server: I would like to create a function that removes specific characters from a string, based on parameters. parameter1 is original string parameter2 is characters want to

Solution 1:

Here is one way using Recursive CTE and Split string function

;WITH data
     AS (SELECT org_string,
                replace_with,
                cs.Item,
                cs.ItemNumber
         FROM   (VALUES ('32.87.65.54.89','87.65' ),
                        ('11.23.45','23' ),
                        ('14.99.16.84','84.14' ),
                        ('11.23.45.65.31.90','23' ),
                        ('34.35.36','35' ),
                        ('34.35.36.76.44.22','35' ),
                        ('34','34' ),
                        ('45.23.11','45.11')) tc (org_string, replace_with)
                CROSS apply [Delimitedsplit8k](replace_with, '.') cs),
     cte
     AS (SELECT org_string,
                replace_with,
                Item,
                Replace('.'+ org_string, +'.'+ Item, '') ASresult,
                ItemNumber
         FROM   data
         WHERE  ItemNumber =1UNIONALLSELECT d.org_string,
                d.replace_with,
                d.Item,
                CASEWHENLEFT(Replace('.'+result, '.'+ d.Item, ''), 1) ='.'THEN Stuff(Replace('.'+result, '.'+ d.Item, ''), 1, 1, '')
                  ELSE Replace('.'+result, '.'+ d.Item, '')
                END,
                d.ItemNumber
         FROM   cte c
                JOIN data d
                  ON c.org_string = d.org_string
                     AND d.ItemNumber = c.ItemNumber +1)
SELECT TOP 1WITH ties org_string,
                       replace_with,
                       result= Isnull(Stuff(result, 1, 1, ''), '')
FROM   cte
ORDERBYRow_number()OVER(partitionBY org_string ORDERBY ItemNumber DESC) 

Result :

╔═══════════════════╦══════════════╦════════════════╗
║    org_string     ║ replace_with ║     result     ║
╠═══════════════════╬══════════════╬════════════════╣
║ 11.23.45          ║ 23           ║ 11.45          ║
║ 11.23.45.65.31.90 ║ 23           ║ 11.45.65.31.90 ║
║ 14.99.16.84       ║ 84.14        ║ 99.16          ║
║ 34                ║ 34           ║                ║
║ 34.35.36          ║ 35           ║ 34.36          ║
║ 34.35.36.76.44.22 ║ 35           ║ 34.36.76.44.22 ║
║ 45.23.11          ║ 45.11        ║ 23             ║
║ 32.87.65.54.89    ║ 87.65        ║ 32.54.89       ║
╚═══════════════════╩══════════════╩════════════════╝

The above code can be converted to a user defined function. I will suggest to create Inline Table valued function instead of Scalar function if you have more records.

Split string Function code referred from http://www.sqlservercentral.com/articles/Tally+Table/72993/

CREATEFUNCTION [dbo].[DelimitedSplit8K]

        (@pStringVARCHAR(8000), @pDelimiterCHAR(1))
RETURNSTABLEWITH SCHEMABINDING ASRETURN--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...-- enough to cover NVARCHAR(4000)WITH E1(N) AS (
                 SELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT1FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT1FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front-- for both a performance gain and prevention of accidental "overruns"SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)SELECT1UNIONALLSELECT t.N+1FROM cteTally t WHERESUBSTRING(@pString,t.N,1) =@pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.SELECT ItemNumber =ROW_NUMBER() OVER(ORDERBY l.N1),
        Item       =SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

Solution 2:

You could try this..

CREATEFUNCTION StringSpecialReplace(@TargetStringVARCHAR(1000),
                                   @InputStringVARCHAR(1000))
returnsVARCHAR(1000)
ASBEGINdeclare@resultasvarchar(1000)

    ;with CTE1 AS
    (
        SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
        FROM (SELECTCAST('<XMLRoot><RowData>'+ REPLACE(@TargetString,'.','</RowData><RowData>') +'</RowData></XMLRoot>'AS XML) AS x)t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ),
    CTE2 AS
    (
        SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
        FROM (SELECTCAST('<XMLRoot><RowData>'+ REPLACE(@InputString,'.','</RowData><RowData>') +'</RowData></XMLRoot>'AS XML) AS x)t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    )

    SELECT@Result=CASEWHEN@ResultISNULLTHEN certs
            ELSE@Result+'.'+ certs
        ENDFROM CTE1 where certs notin (select certs from CTE2)

    return@ResultEND

Solution 3:

CREATEFUNCTION MyRemoveFunc (
            @OrigStringVARCHAR(8000),
             @RemovedStringVARCHAR(max)
            )
    RETURNSvarchar(max)
    BEGINDECLARE@xml XML 
            SET@xml='<n>'+REPLACE(@RemovedString,'.','</n><n>')+'</n>'SET@OrigString='.'+@OrigString+'.'SELECT@OrigString=REPLACE(@OrigString,'.'+s.b.value('.','varchar(200)')+'.','.')
            FROM@xml.nodes('n')s(b)
           RETURNCASEWHEN LEN(@OrigString)>2THENSUBSTRING(@OrigString,2,LEN(@OrigString)-2) ELSE''ENDENDSELECT t.*,dbo.MyRemoveFunc(t.o,t.r)
     FROM   (VALUES ('32.87.65.54.89','87.65' ),
                            ('11.23.45','23' ),
                            ('14.99.16.84','84.14' ),
                            ('11.23.45.65.31.90','23' ),
                            ('34.35.36','35' ),
                            ('34.35.36.76.44.22','35' ),
                            ('34','34' ),
                            ('45.23.11','45.11')) t (o, r)

o                 r     
----------------- ----- -------------------------
32.87.65.54.89    87.65 32.54.89
11.23.45          23    11.45
14.99.16.84       84.14 99.16
11.23.45.65.31.90 23    11.45.65.31.90
34.35.36          35    34.36
34.35.36.76.44.22 35    34.36.76.44.22
34                34    
45.23.11          45.11 23