SQL Server将一列的多行内容拼接成一行的实现方法
下面大家先看下示例代码：
昨天遇到一个SQL Server的问题：需要写一个储存过程来处理几个表中的数据，最后问题出在我想将一个表的一个列的多行内容拼接成一行，比如表中有两列数据 ：
我想把这个表变成如下格式：
企业1，企业2，企业3
企业4，企业5
一开始挺头疼的（会了的肯定没有这种感觉，不会那必须是头疼啊(*^__^*) ），从网上找了点资料，算是找到一种比较简单方便的方法吧，现在大体总结一下，供大家共同学习。
原先的表名为Table_A，实现代码如下：
(select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
from Table_A as A group by 类别
这里使用了SQL Server 2005版本以后加入的stuff以及for xml path，先说下在上面这句sql中的作用，然后再详细的说明一下这两个的用法。
for xml path('')
这句是把得到的内容以XML的形式显示。
stuff((select ',' + ep_name from ep_detail where ep_classes = a.ep_classes for xml path('')), 1, 1, '')
这句是把拼接的内容的第一个“，”去掉
stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。
•param1：一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。
•startIndex：一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。
•length：一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。
•param2,返回类型。如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。
select STUFF('abcdefg',1,0,'1234')
--结果为'1234abcdefg'
select STUFF('abcdefg',1,1,'1234')
--结果为'1234bcdefg'
select STUFF('abcdefg',2,1,'1234')
--结果为'a1234cdefg'
select STUFF('abcdefg',2,2,'1234')
--结果为'a1234defg'
for xml path
for xml path有的人可能知道有的人可能不知道，其实它就是将查询结果集以XML形式展现，有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.
假设有个表存放着学生的选课情况（stu_courses）：
接下来我们来看应用FOR XML PATH的查询结果语句如下：
select stu_name,stu_course from stu_
结果如下：
由此可以看出 FOR XML PATH 可以将查询结果根据行输出成XML各式！
顶一下(0) 踩一下(0)
热门标签：字符串操作函数& 1、QUOTENAME()&返回被特定字符括起来的字符串。&QUOTENAME&(&’character_expression’&[，&quote_&character])&其中&quote_&character&标&明括字符串所用的字符，缺省值为“[]”。& 2、REPLICATE()&返回一个重复&character_expression&指定次数的字符串。&REPLICATE&(character_expression&integer_expression)&如果&integer_expression&值为&负值，则返回&NULL&。& 3、REVERSE()&将指定的字符串的字符排列顺序颠倒。&REVERSE&(&character_expression&)&其中&character_expression&可以是字符串、常数或&一个列的值。& 4、REPLACE()&返回被替换了指定子串的字符串。&REPLACE&(&string_expression1&，&&string_expression2&，&&string_expression3&)&用&string_expression3&替换在&string_expression1&中的子串&string_expression2。& 4、SPACE()&返回一个有指定长度的空白字符串。&SPACE&(&integer_expression&)&如果&integer_expression&值为负值，则返回&NULL&。& 5、STUFF()&用另一子串替换字符串指定位置、长度的子串。&STUFF&(&character_expression1&，&&start_&position&，&&length&，&&character_expression2&)&如果起始位置为负或长度值为负，或者起始位置大于&character_expression1&的长度，则&返回&NULL&值。&如果&length&长度大于&character_expression1&中&start_&position&以右的长度，则&character_expression1&只保留首字符。& &
【】【】【】【】
ISBN编号:&8
出版时间:&2013-3
出版社:&中国人事出版社
定价：￥45 优惠价：￥45&&ISBN编号:&9
出版时间:&2013-4
出版社:&中国人事出版社
定价：￥45 优惠价：￥45&&
????????????
????????????
　 　 　 　 　Copyright ©
() All Rights Reserved以下所有例子均Studnet表为例：&
计算字符串长度len（）用来计算字符串的长度
select sname ,len(sname) from student
字符串转换为大、小写lower() 用来将一个字符串转换为小写，upper() 用来将一个字符串转换为大写
select lower('I AM A STUDENT !')select upper('i am a student !')
截去字符串左、右侧空格&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ltrim() 用来将一个字符串左侧的空格去掉 ，rtrim()用来将一个字符串右侧的空格去掉
declare @str varchar(100)set @str='
我的左侧有空格！'select @str as 初始字符, len(@str) as 初始长度,ltrim(@str) as 现有字符,len(ltrim(@str)) as 现有长度
返回由重复的空格组成的字符串space(integer_expression)&& &integer_expression 指示空格个数的正整数。如果 integer_expression 为负，则返回空字符串。
select 'A'+ space(2)+'B'
取子字符串substring(string,start_position,length) 可以从任意位置取任意长度的子字符串，left(string,length)& 从左侧开始取子字符串right(string,length)从右侧开始取子字符串
substring('HelloWorld!',6,6)select
left('HelloWorld!'&,5)select
right('HelloWorld!'&,6)
字符串替换replace（string,要被替换的字符串，替换的字符串）
replace('HelloWorld!','o','e')
结果为:HelleWerld!
返回字符串值的逆向值reverse(string_expression)
select reverse('abc')
结果为：cba
删除指定长度的字符，并在指定的起点处插入另一组字符stuff(character_expression , start , length ,character_expression)start 一个整数值，指定删除和插入的开始位置。length 一个整数，指定要删除的字符数。
select stuff('abcdefg',1,6,'Hello ')
结果为：Hello g
以指定的次数重复字符串值replicate(string_expression ,integer_expression)
select replicate('abc',4)
结果为：abcabcabcabc
返回字符串中指定表达式的开始位置charindex(expression1 ,expression2& , start_location )或charindex(expression1 ,expression2 ）expression1在expression2 中的开始位置
select charindex('H','elloHWorld')
返回指定表达式中模式第一次出现的开始位置patindex('%pattern%',expression) 返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
select patindex('%Hello%','WorldHello')
返回输入表达式的第一个字符的整数值unicode( 'ncharacter_expression' )&&& ' ncharacter_expression ' 为 nchar
或 nvarchar 表达式。
select unicode('a')
结果为：97select unicode('abc')
结果为：97
返回由数字数据转换来的字符数据str(float_expression , length ,decimal )float_expression 带小数点的近似数字 (float) 数据类型的表达式。length&&&&&&&&&&&&&& 总长度。它包括小数点、符号、数字以及空格。默认值为 10。decimal&&&&&&&&&&&& 小数点右边的小数位数。decimal 必须小于等于 16。如果 decimal 大于 16，则将结果截断为小数点右边的 16 位。
select str(123.436,2),
len(str(123.436,2))
//当表达式超出指定长度时，字符串为指定长度返回 **select str(123.436),
len(str(123.436)),
str(123.436,6),
len(str(123.436,6)),
str(123.436,6,1), len(str(123.436,6,1)) //由六个数字和一个小数点组成的表达式转换为有六个位置的字符串。
数字的小数部分舍入为一个小数位。 select str(),
len(str()),
len(str(,6)),
str(,6,1),len(str(,6,1))
得到字符的ASCII码ascii()用来得到一个字符的ASCII码，它有且只有一个参数，如果参数为字符串，那么取第一个字符的ASCII码
ascii('H')select
ascii('HelloWorld!')
得到一个与ASCII码数字对应的字符Char(integer_expression)& integer_expression 介于 0 和 255 之间的整数。如果该整数表达式不在此范围内，将返回 NULL 值。
返回返回具有指定的整数代码的 Unicode 字符nchar(integer_expression)& integer_expression 介于 0 与 65535 之间的正整数。如果指定了超出此范围的值，将返回 NULL。
select nchar(1000)
返回带有分隔符的 Unicode 字符串，分隔符的加入可使输入的字符串成为有效的 SQL Server 分隔标识符。quotename('character_string')& character_string 不得超过 128 个字符。超过 128 个字符的输入将返回 NULL。
select quotename('abc[aa]def')
结果为：[abc[]]def]& 请注意，字符串 abc[]def 中的右方括号有两个，用于指示转义符。select QUOTENAME('abcdef','''') --分隔符是两个单引号-- 'abcdef'select QUOTENAME('abcdef') --分隔符是]-- [abcdef]select QUOTENAME('abcdef','{}') --分隔符是}-- {abcdef}
发音匹配度有时候我们并不知道一个人姓名的拼写，但是我们知道他的发音，这时我们可以进行发音的匹配度测试。soundex（）用于计算一个字符串的发音特征性，这个特征值为一个四个字符的字符串，特征性的第一个字符总是初始字符串中的第一个字符，而后是一个三位数的数值。
select sname ,soundex(sname) from student
发音特征值的含义非常复杂，如果要根据两个发音特征值来分析两个字符串的发音相似度的话非常麻烦。可以使用difference（）来简化两个字符串的发音相似度比较，它可以计算两个字符串的发音特征值，并且比较它们，然后返回一个0~4之间的值来反映两个字符串的发音相似度，这个值越大则表示两个字符串的发音相似度越大。
select sname,soundex(sname), difference(sname,'Herry') from stu
结果为：&&&
阅读(...) 评论()SQL对字符串的处理能力比较弱，比如我要循环遍历象1,2,3,4,5这样的字符串，如果用数组的话，遍历很简单，但是T-SQL不支持数组，所以处理下来比较麻烦。下边的函数，实现了象数组一样去处理字符串。
一,用临时表作为数组 复制代码 代码如下: create function f_split(@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin
while(charindex
一,用临时表作为数组 复制代码 代码如下: create function f_split(@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin while(charindex(@split,@c)&&0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end go select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',') drop function f_split col -------------------- dfkd dfdkdf dfdkf dffjk
（所影响的行数为 4 行） 二、按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。 复制代码 代码如下: CREATE function Get_StrArrayLength ( @str varchar(1024), --要分割的字符串 @split varchar(10) --分隔符号 ) returns int as begin declare @location int declare @start int declare @length int set @str=ltrim(rtrim(@str)) set @location=charindex(@split,@str) set @length=1 while @location&&0 begin set @start=@location+1 set @location=charindex(@split,@str,@start) set @length=@length+1 end return @length end
调用示例：select dbo.Get_StrArrayLength('78,1,2,3',',') 返回值：4 三、按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便 复制代码 代码如下: CREATE function Get_StrArrayStrOfIndex ( @str varchar(1024), --要分割的字符串 @split varchar(10), --分隔符号 @index int --取第几个元素 ) returns varchar(1024) as begin declare @location int declare @start int declare @next int declare @seed int set @str=ltrim(rtrim(@str)) set @start=1 set @next=1 set @seed=len(@split) set @location=charindex(@split,@str) while @location&&0 and @index&@next begin set @start=@location+@seed set @location=charindex(@split,@str,@start) set @next=@next+1 end if @location =0 select @location =len(@str)+1 --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。 return substring(@str,@start,@location-@start) end
调用示例：select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2) 返回值：9 三、结合上边两个函数，象数组一样遍历字符串中的元素 复制代码 代码如下: declare @str varchar(50) set @str='1,2,3,4,5' declare @next int set @next=1 while @next&=dbo.Get_StrArrayLength(@str,',') begin print dbo.Get_StrArrayStrOfIndex(@str,',',@next) set @next=@next+1 end
调用结果： 1 2 3 4 5SQL字符串处理函数大全
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了sqlserver中常用的字符串处理函数，需要的朋友可以参考下
select语句中只能使用sql函数对字段进行操作（链接sql server），select 字段1 from 表1 where 字段1.IndexOf("云")=1;这条语句不对的原因是indexof（）函数不是sql函数，改成sql对应的函数就可以了。left（）是sql函数。select 字段1 from 表1 where charindex（'云',字段1）=1;
字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。常用的字符串函数有：一、字符转换函数1、ASCII()返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘'括起来，但含其它字符的字符串必须用‘'括起来使用，否则会出错。2、CHAR()将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。3、LOWER()和UPPER()LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。4、STR()把数值型数据转换为字符型数据。STR (&float_expression&[，length[， &decimal&]])length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。当length 或者decimal 为负值时，返回NULL；当length 小于小数点左边（包括符号位）的位数时，返回length 个*；先服从length ，再取decimal ；当返回的字符串位数小于length ，左边补足空格。二、去空格函数1、LTRIM() 把字符串头部的空格去掉。2、RTRIM() 把字符串尾部的空格去掉。三、取子串函数1、left() LEFT (&character_expression&， &integer_expression&)返回character_expression 左起 integer_expression 个字符。2、RIGHT() RIGHT (&character_expression&， &integer_expression&)返回character_expression 右起 integer_expression 个字符。3、SUBSTRING()SUBSTRING (&expression&， &starting_ position&， length)返回从字符串左边第starting_ position 个字符起length个字符的部分。四、字符串比较函数1、CHARINDEX()返回字符串中某个指定的子串出现的开始位置。CHARINDEX (&'substring_expression'&， &expression&)其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。2、PATINDEX()返回字符串中某个指定的子串出现的开始位置。PATINDEX (&'%substring _expression%'&， &column_ name&)其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。五、字符串操作函数1、QUOTENAME()返回被特定字符括起来的字符串。QUOTENAME (&'character_expression'&[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。2、REPLICATE()返回一个重复character_expression 指定次数的字符串。REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。3、REVERSE()将指定的字符串的字符排列顺序颠倒。REVERSE (&character_expression&) 其中character_expression 可以是字符串、常数或一个列的值。4、REPLACE()返回被替换了指定子串的字符串。REPLACE (&string_expression1&， &string_expression2&， &string_expression3&) 用string_expression3 替换在string_expression1 中的子串string_expression2。4、SPACE()返回一个有指定长度的空白字符串。SPACE (&integer_expression&) 如果integer_expression 值为负值，则返回NULL 。5、STUFF()用另一子串替换字符串指定位置、长度的子串。STUFF (&character_expression1&， &start_ position&， &length&，&character_expression2&)如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。六、数据类型转换函数1、CAST()CAST (&expression& AS &data_ type&[ length ])2、CONVERT()CONVERT (&data_ type&[ length ]， &expression& [， style])1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。2）length用于指定数据的长度，缺省值为30。3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。七、日期函数1、day(date_expression) 返回date_expression中的日期值2、month(date_expression)返回date_expression中的月份值3、year(date_expression)返回date_expression中的年份值4、DATEADD()DATEADD (&datepart&， &number&， &date&)返回指定日期date 加上指定的额外日期间隔number 产生的新日期。5、DATEDIFF()DATEDIFF (&datepart&， &date1&， &date2&)返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。6、DATENAME()DATENAME (&datepart&， &date&)以字符串的形式返回日期的指定部分此部分。由datepart 来指定。7、DATEPART()DATEPART (&datepart&， &date&)以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART (dd， date) 等同于DAY (date)DATEPART (mm， date) 等同于MONTH (date)DATEPART (yy， date) 等同于YEAR (date)8、GETDATE()以DATETIME 的缺省格式返回系统当前的日期和时间。
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具}