DatabaseProcApplicationCreatedLinks
sybsystemprocssp_replace_string_plus  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_REPLACE_STRING_PLUS
4     **
5     **	A utility procedure to compact a given string by replacing all
6     **	contiguous occurances of a subtring with a sinlge occurance of the given
7     **	replacement string. Can be used by any procedure, but currently called 
8     **	by sp_spaceusage* set of procedures.
9     **	
10    **	Paramter
11    **		@str		- The original string.	
12    **		@substr		- The substring to be replaced.	
13    **		@rplcsubstr	- The replacement substring.
14    **		@casesensitive	- Whether case sensitive mode/not?
15    **	
16    **	Returns
17    **		- The resultant subtring replaced string.
18    **
19    **		0 - if all goes well
20    **		1 - if @str is NULL, @substr is NULL
21    {
22    */
23    create procedure sp_replace_string_plus
24    (
25        @str varchar(512)
26        , @substr varchar(512)
27        , @rplcsubstr varchar(512)
28        , @casesensitive bit = 1
29        , @replacedstr varchar(512) output
30    )
31    as
32        begin -- {	
33    
34            declare @l varchar(512)
35                , @r varchar(512)
36                , @rest varchar(512)
37                , @n int
38                , @i int
39                , @retval int
40                , @maxsize int
41                , @newsize int
42    
43            select @maxsize = 512
44    
45            exec @retval = sp_substring_count @str, @substr, @casesensitive, @n out
46    
47            select @newsize = datalength(@str)
48                + (datalength(@rplcsubstr) - datalength(@substr)) * @n
49    
50            if @retval = 1 or @n = 0 or @newsize > @maxsize
51            begin
52                select @replacedstr = @str
53                return (1)
54            end
55            else
56            begin
57                select @rest = @str
58                    , @replacedstr = NULL
59                    , @l = NULL
60                    , @r = NULL
61                    , @i = @n
62    
63                while @i > 0
64                begin
65                    exec sp_split_string @rest, @substr, @casesensitive
66                        , @l out, @r out
67                    if @l is not NULL or @i = @n
68                    begin
69                        select @replacedstr =
70                            @replacedstr + @l + @rplcsubstr
71                    end
72    
73                    select @rest = @r
74                        , @i = @i - 1
75    
76                end
77    
78                select @replacedstr = @replacedstr + @rest
79                return (0)
80            end
81    
82        end -- }	-- }	
83    


exec sp_procxmode 'sp_replace_string_plus', 'AnyMode'
go

Grant Execute on sp_replace_string_plus to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_replace_string_plus  
 MNER 3 No Error Check should check return value of exec 45
 MNER 3 No Error Check should check return value of exec 65
 MUCO 3 Useless Code Useless Brackets in create proc 24
 MUCO 3 Useless Code Useless Begin-End Pair 32
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 79
 MTR1 2 Metrics: Comments Ratio Comments: 35% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 6dec - 3exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 22 23

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_split_string  
calls proc sybsystemprocs..sp_substring_count  
   calls proc sybsystemprocs..sp_split_string  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_paramcheck  
   called by proc sybsystemprocs..sp_spaceusage