sqlserver字符串分隔与拼接实例.docx
sqlserver字符串分隔与拼接实例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) As Begin declare resultStr nvarchar(4000) set resultStr='' declare Index int set Index=charindex(',',IdList) if Index is null OR Index=0 -如果不存在逗号(只有一个 或者 为null) begin select resultStr=BName from B where BID=IdList return resultStr end declare BID varchar(3) while Index>0 begin 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; end end return resultStr End -查询Sql语句如下： select AID,dbo.Func_GetNameList(BList) as BListName from A 附加一：使用部分字符串函数charindex、stuff的用法 ·CHARINDEX函数 charindex('要搜索的字符串','列名 或 整体全部字符串',查询起始索引) 注：sqlserver中索引从1开始 例如： print charindex('abc','dfsaabc')-存在所以返回值为5 charindex函数的第一个参数不能是null 而且 必须是以下类型： char/varchar、nchar/nvarchar 和 binary/varbinary。 charindex函数的第二个参数可以是null,但结果返回null 当第二个参数不是null时。 判断第一个字符串是否在第二个字符串中存在， 如果存在，返回第一个字符串的第一个字符在第二个字符串中的下标(下标从1开始)，不存在返回0 charindex函数的第三个参数可以忽略 此时 按从第一个字符开始查询匹配后的索引。 charindex函数的第三个参数为null时 返回null charindex函数的第三个参数为负整数或0时 此时 按从第一个字符开始查询匹配后的索引。 示例如下： select charindex('a','3abcdsad') -返回2 (从起始字符开始查找) select charindex('a','3abcdsad',null) -返回null select 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 varchar(8000) set Str = '' select Str = Str +',' + ISNull(BuMenName,'') from ERPUserGuanliDept where UserName = UserName if(Str<>'') set Str=substring(Str,2,len(Str)-1) return Str END
