sqlserver字符串分隔与拼接实例.doc

Sql字符串分隔与拼接示例题目：已知A,B两表 基本数据如下：A表(BList是B表的BID集合，用逗号连接)：B表：获得查询结果如下：我们的思路如下：创建一个函数，参数是IDList,由ID拼接的字符串，返回值是NameList，由Name拼接的字符串。调用该函数即可。详细的sql语句如下(附带建表及插入测试数据)：create table B(BID varchar(3) primary key,BName varchar(20)insert into B(BID,BName) values (001,蔬菜)insert into B(BID,BName) values (002,水果)insert into B(BID,BName) values (003,牙膏)insert into B(BID,BName) values (004,洗发水)create table A(AID int,BList varchar(4000)-BList是由BID用逗号连接组成的字符串insert into A(AID,BList) values (1,001,002)insert into A(AID,BList) values (2,001)insert into A(AID,BList) values (3,002,003)insert into A(AID,BList) values (4,002)insert into A(AID,BList) values (5,001,002,003)insert into A(AID,BList) values (6,002,004)insert into A(AID,BList) values (7,001,002,003,004)-我们创建一个函数Func_GetNameList。create function Func_GetNameList(IdList varchar(4000)-由逗号拼接 001,002,003 组成的字符串 转换为Name组成的字符串 即 蔬菜,水果,牙膏returns nvarchar(4000)AsBegindeclare resultStr nvarchar(4000)set resultStr=declare Index intset Index=charindex(,IdList)if Index is null OR Index=0 -如果不存在逗号(只有一个 或者 为null)beginselect resultStr=BName from B where BID=IdListreturn resultStrenddeclare BID varchar(3)while Index0begin set BID=substring(IdList,1,Index-1) if(resultStr=) select resultStr=BName from B where BID=BID else select resultStr=resultStr+,+BName from B where BID=BID set IdList=stuff(IdList,1,Index,) -删除第一个逗号前面的字符串 set Index=charindex(,IdList) if(Index=0) -如果是最后一个 begin select resultStr=resultStr+,+BName from B where BID=IdList break; endendreturn resultStrEnd-查询Sql语句如下：select AID,dbo.Func_GetNameList(BList) as BListName from A附加一：使用部分字符串函数charindex、stuff的用法CHARINDEX()函数charindex(要搜索的字符串,列名 或 整体全部字符串,查询起始索引)注：sqlserver中索引从1开始例如：print charindex(abc,dfsaabc)-存在所以返回值为5（abc中的a在dfsaabc里的下标）charindex函数的第一个参数不能是null 而且 必须是以下类型：char/varchar、nchar/nvarchar 和 binary/varbinary。charindex函数的第二个参数可以是null,但结果返回null当第二个参数不是null时。判断第一个字符串是否在第二个字符串中存在，如果存在，返回第一个字符串的第一个字符在第二个字符串中的下标(下标从1开始)，不存在返回0 charindex函数的第三个参数可以忽略 此时 按从第一个字符开始查询匹配后的索引。charindex函数的第三个参数为null时 返回nullcharindex函数的第三个参数为负整数或0时 此时 按从第一个字符开始查询匹配后的索引。示例如下：select charindex(a,3abcdsad) -返回2 (从起始字符开始查找)select charindex(a,3abcdsad,null) -返回nullselect charindex(a,3abcdsad,-5) -返回2 (从起始字符开始查找)select charindex(a,3abcdsad,0) -返回2 (从起始字符开始查找)select charindex(a,3abcdsad,4) -返回7 (从第四个字符开始查找)查询DocumentSummary字段中包含bicycle的所有行。一般大家都会写成这样:select * from Production.Documentwhere DocumentSummary like%bicycle%了解这个函数以后,大家可以这样写:select * from Production.Document where charindex(bicycle,DocumentSummary)0 这种方法比like%的形式速度上要快很多.STUFF() 函数将一字符串中的某一部分用另一个字符串替换掉。语法STUFF(原字符串, 开始替换的位置, 被替换的字符数, 用于替换的字符串)LEFT(),Right()函数返回某个被请求部分的左右侧部left(字符串,长度) right(字符串,长度) datalength (),len ()函数取字符串字节数用函数datalength(字符串) 返回任何表达式所占用的字节数。取字符串字符数用函数len(字符串) -返回字符数附加二：sql分隔与拼接函数-分隔函数-分隔字符串create function f_splitstr(SourceSql varchar(8000),StrSeprate varchar(100) returns temp table(F1 varchar(100) as begin declare ch as varchar(100) set SourceSql=SourceSql+StrSeprate while(SourceSql) begin set ch=left(SourceSql,charindex(,SourceSql,1)-1) insert temp values(ch) set SourceSql=stuff(SourceSql,1,charindex(,SourceSql,1),) end return end -测试select * from dbo.f_splitstr(a,b,c,d,)-合并函数Create FUNCTION JoinString -合并字符串 多行合并为一行( UserName varchar(50) ) RETURNS varchar(8000) AS BEGIN declare Str v