SQLServer字符串操作存储过程
获取某个表中特定字段的所有字符串形式
1 /*****************************************************************************
2 * Name: sp_GetString
3 * Author: Ridge Wong
4 * Create Date: 2005年11月24日 15:16:43
5 * Version: V1.0.1
6 * usage: Get some FIELDs value colletion of one TABLE by your Sperator
7 * Example:
8 * --[RUN]
9 * exec sp_GetString 'tbl_Ring_size','rs_id','rs_id,rsize',',','|'
10 * exec sp_GetString 'tbl_Ring_size','rs_id','rsize'
11 * --[RETURN]
12 * 1,mid|2,midi|3,wav|4,mmf|5,pmd|6,imy|7,amr|8,mld|9,adp|10,wma|11,mp3|12,mfm
13 * mid|midi|wav|mmf|pmd|imy|amr|mld|adp|wma|mp3|mfm
14 * ---------------------------------------------------
15 * remarks: Note Support a field's value is null.
16 *****************************************************************************/
17 ALTER PROCEDURE [dbo].[sp_GetString] (
18 @tblName nvarchar(20), -- 表名
19 @getIdName nvarchar(20), -- 主键ID
20 @getFieldNames varchar(1000), -- 获取字段列表，不支持通配符号*。
21 @colSperator varchar(500) = ',', -- 列分隔符
22 @rowSperator varchar(500) = '|') -- 行分割符
23 AS
24
25 declare @eof bit
26 declare @id int
27 declare @result varchar(8000)
28 declare @oTemp varchar(4000)
29 declare @sql nvarchar(4000)
30
31 set @eof = 0
32 set @id = 0
33 set @result = ''
34
35 -- 判断是否是多列
36 if CHARINDEX(',',@getFieldNames,1)>0
37 set @getFieldNames = 'Convert(nvarchar(4000),' + Replace(@getFieldNames,',',')
38 + '+ char(39) + @colSperator + char(39) + ' + Convert(nvarchar(4000),')
39 + ')'
40
41
42 while (@eof = 0)
43 begin
44
45 set @sql = 'select @id = IsNull((select top 1 '+ @getIdName + ' from ' + @tblName + ' where ' + @getIdName + '>=@id),0)'
46 exec sp_executesql @sql,
47 N'@id int output',
48 @id output
49
50 set @sql = 'select @oTemp = (select top 1 '+ @getFieldNames +' from '+ @tblName +' where '+ @getIdName +' = '+ cast(@id as varchar(20)) +')'
51 exec sp_executesql @sql,
52 N'@oTemp varchar(4000) output',
53 @oTemp output
54
55 if @id = 0
56 set @eof = 1
57 else
58 begin
59 set @result = @result + @rowSperator + @oTemp
60 set @id = @id + 1
61 end
62 end
63
64 set @result = SubString(@result,len(@rowSperator)+1,len(@result)-len(@rowSperator))
65 select @result as ResultString
66
2 * Name: sp_GetString
3 * Author: Ridge Wong
4 * Create Date: 2005年11月24日 15:16:43
5 * Version: V1.0.1
6 * usage: Get some FIELDs value colletion of one TABLE by your Sperator
7 * Example:
8 * --[RUN]
9 * exec sp_GetString 'tbl_Ring_size','rs_id','rs_id,rsize',',','|'
10 * exec sp_GetString 'tbl_Ring_size','rs_id','rsize'
11 * --[RETURN]
12 * 1,mid|2,midi|3,wav|4,mmf|5,pmd|6,imy|7,amr|8,mld|9,adp|10,wma|11,mp3|12,mfm
13 * mid|midi|wav|mmf|pmd|imy|amr|mld|adp|wma|mp3|mfm
14 * ---------------------------------------------------
15 * remarks: Note Support a field's value is null.
16 *****************************************************************************/
17 ALTER PROCEDURE [dbo].[sp_GetString] (
18 @tblName nvarchar(20), -- 表名
19 @getIdName nvarchar(20), -- 主键ID
20 @getFieldNames varchar(1000), -- 获取字段列表，不支持通配符号*。
21 @colSperator varchar(500) = ',', -- 列分隔符
22 @rowSperator varchar(500) = '|') -- 行分割符
23 AS
24
25 declare @eof bit
26 declare @id int
27 declare @result varchar(8000)
28 declare @oTemp varchar(4000)
29 declare @sql nvarchar(4000)
30
31 set @eof = 0
32 set @id = 0
33 set @result = ''
34
35 -- 判断是否是多列
36 if CHARINDEX(',',@getFieldNames,1)>0
37 set @getFieldNames = 'Convert(nvarchar(4000),' + Replace(@getFieldNames,',',')
38 + '+ char(39) + @colSperator + char(39) + ' + Convert(nvarchar(4000),')
39 + ')'
40
41
42 while (@eof = 0)
43 begin
44
45 set @sql = 'select @id = IsNull((select top 1 '+ @getIdName + ' from ' + @tblName + ' where ' + @getIdName + '>=@id),0)'
46 exec sp_executesql @sql,
47 N'@id int output',
48 @id output
49
50 set @sql = 'select @oTemp = (select top 1 '+ @getFieldNames +' from '+ @tblName +' where '+ @getIdName +' = '+ cast(@id as varchar(20)) +')'
51 exec sp_executesql @sql,
52 N'@oTemp varchar(4000) output',
53 @oTemp output
54
55 if @id = 0
56 set @eof = 1
57 else
58 begin
59 set @result = @result + @rowSperator + @oTemp
60 set @id = @id + 1
61 end
62 end
63
64 set @result = SubString(@result,len(@rowSperator)+1,len(@result)-len(@rowSperator))
65 select @result as ResultString
66
