1 /*
2 ** SP_SPLIT_STRING
3 **
4 ** A utility procedure to split a given string at the first occurance of a
5 ** given sub string into two sub strings - one to the left and one to the
6 ** right of the first occurance of the given sub string. This can be done
7 ** in a case sensitive or case insensitive mode. By default it would be
8 ** case sensitive. Can be used by any procedure, although currently called
9 ** by sp_spaceusage* set of procedures.
10 **
11 ** Paramter
12 ** @str - The string to split.
13 ** @substr - The substring around which to split @str.
14 ** @casesensitive - Whether case sensitive mode/not?
15 **
16 ** Returns
17 ** - The left substring.
18 ** - The right substring.
19 **
20 ** NOTE: No trimming of white space is done.
21 **
22 ** 0 - if all goes well
23 ** 1 - substring not found or NULL, string NULL
24 {
25 */
26 create procedure sp_split_string
27 (
28 @str varchar(512)
29 , @substr varchar(512)
30 , @casesensitive bit = 1
31 , @left varchar(512) output
32 , @right varchar(512) output
33 )
34 as
35 begin -- {
36
37 declare @substrIndex int
38 , @origstr varchar(512)
39
40 -- Save the original string as left and right substrings need to be
41 -- extracted from it later. This is needed in case this procedure is
42 -- to be used in case-insensitive mode.
43 --
44 select @origstr = @str
45
46 if @casesensitive = 0
47 begin
48 select @str = lower(@str)
49 , @substr = lower(@substr)
50 end
51
52 select @substrIndex = charindex(@substr, @str)
53
54 if @substrIndex = 0 or @substrIndex is NULL
55 begin
56 select @left = NULL
57 , @right = NULL
58 return (1)
59 end
60
61 select @left = substring(@origstr, 1, @substrIndex - 1)
62 , @right = substring(@origstr, @substrIndex
63 + char_length(@substr),
64 char_length(@origstr)
65 - (@substrIndex - 1
66 + char_length(@substr)))
67 return (0)
68
69 end -- } -- }
70
exec sp_procxmode 'sp_split_string', 'AnyMode'
go
Grant Execute on sp_split_string to public
go
MGTP 3 Grant to public sybsystemprocs..sp_split_string
MTR1 2 Metrics: Comments Ratio Comments: 55%
