DatabaseProcApplicationCreatedLinks
sybsystemprocssp_substring_count  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SUBSTRING_COUNT
4     **
5     **	A utility procedure to count the number of times a given substring
6     **	occurs in a given string. This can be done in a case sensitive, or 
7     **	case insensitive mode. By default it would be case sensitive. Can be
8     **	used by any procedure, although currently called by sp_spaceusage* set
9     **	of procedures.
10    **	
11    **	Paramter
12    **		@str		- The original string.	
13    **		@substr		- The substring whose occurance within @str is
14    **				  to be counted.
15    **		@casesensitive	- Whether case sensitive mode/not?
16    **	
17    **	Returns
18    **		- The number of times the substring occurs in string.
19    **
20    **		0 - if all goes well
21    **		1 - substring not found
22    {
23    */
24    create procedure sp_substring_count
25    (
26        @str varchar(512)
27        , @substr varchar(512)
28        , @casesensitive bit = 1
29        , @count int output
30    )
31    as
32        begin -- {	
33    
34            declare @substrIndex int
35                , @left varchar(512)
36                , @right varchar(512)
37                , @rest varchar(512)
38                , @retVal int
39    
40            if @casesensitive = 0
41            begin
42                select @str = lower(@str)
43                    , @substr = lower(@substr)
44            end
45    
46            select @substrIndex = charindex(@substr, @str)
47                , @count = 0
48                , @rest = @str
49    
50            if @substrIndex = 0 or @substrIndex is NULL
51            begin
52                return (1)
53            end
54    
55            while (1 = 1)
56            begin
57                exec @retVal = sp_split_string @rest, @substr, 1, @left out
58                    , @right out
59    
60                if @right is NULL and @retVal = 1
61                    break
62    
63                select @rest = @right
64                    , @count = @count + 1
65    
66            end
67            return (0)
68    
69        end -- }	-- }
70    


exec sp_procxmode 'sp_substring_count', 'AnyMode'
go

Grant Execute on sp_substring_count to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_substring_count  
 MUCO 3 Useless Code Useless Brackets in create proc 25
 MUCO 3 Useless Code Useless Begin-End Pair 32
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 67
 MTR1 2 Metrics: Comments Ratio Comments: 43% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 6dec - 2exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 19 24

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_split_string  

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