--声明一个字符串变量
declare @s varchar(100)
--设置为空字符串,如果不设置,则最后的返回值为null
set @s=''
--将符合条件的col字段串连起来组成一个字符串
select @s=@s+','+col from table where ..
--去掉字符串开头多余的","号
stuff(@s,1,1,'')
declare @s varchar(100)
--设置为空字符串,如果不设置,则最后的返回值为null
set @s=''
--将符合条件的col字段串连起来组成一个字符串
select @s=@s+','+col from table where ..
--去掉字符串开头多余的","号
stuff(@s,1,1,'')
解决方案 »
- SQL server 2008R2 连接不上、求帮助
- 如何查询远程链接服务器的设置信息?
- 服务器
- 有什么函数可以把日期型字段的年月日取出来,不要时间信息。
- 两个系统存储过程之间的奇怪现象
- 怎么查找datetime 为空的字段?
- 我是建一张表,表中有一列是email 输入。email 是用什么数据类型的啊
- 求SQL语句
- ADO导出的数据集可否除了用ADOTable1->FieldByName("KEY")->Value获得表中数据,还有其他办法吗?
- SQL Server题目,这应该怎么做,帮帮忙谢谢大家!
- 如何取得记录集中某一段数据,一旦通过,立即给分!!!
- 在Asp页面里无法执行,但sql查询分析器里可以!!
删除指定长度的字符并在指定的起始点插入另一组字符。语法
STUFF ( character_expression , start , length , character_expression ) 参数
character_expression由字符数据组成的表达式。character_expression 可以是常量、变量,也可以是字符或二进制数据的列。start是一个整形值,指定删除和插入的开始位置。如果 start 或 length 是负数,则返回空字符串。如果 start 比第一个 character_expression 长,则返回空字符串。length是一个整数,指定要删除的字符数。如果 length 比第一个 character_expression 长,则最多删除到最后一个 character_expression 中的最后一个字符。返回类型
如果 character_expression 是一个支持的字符数据类型,则返回字符数据。如果 character_expression 是一个支持的 binary 数据类型,则返回二进制数据。注释
可以嵌套字符串函数。示例
下例通过在第一个字符串 (abcdef) 中删除从第二个位置(字符 b)开始的三个字符,然后在删除的起始位置插入第二个字符串,创建并返回一个字符串。 SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO下面是结果集:---------
aijklmnef (1 row(s) affected)
select @s=@s+'a'
Introduction
I discovered an interesting string technique the other day while reading a SQL programming book. It presented a great way that one can build a string with a delimited list of items. It can also be used to "bend" a result set from a series of rows to a column. I am sure many of you can come up with a whole bunch of ideas that I would never think of, but this actually solved a problem that I had previously used a cursor to solve. And eliminating cursors is almost always a good thing. The Problem
I have a number of email integration processes that send notifications to people. Most of these are business processes and the names of the people who recieve the emails can change at any time. We store these emails in a table called, appropriately, email_notification. This table is defined with the following DDL: create table email_notification
( id int identity( 1, 1),
process varchar( 80),
recipients varchar( 255),
cc varchar( 255)
)and contains data like the following:
id process recipients cc
---- -------------------- ----------------------------------------------------- ------
1 Business Process 1 [email protected];[email protected]
2 Business Process 2 [email protected]
3 Business Process 3 [email protected];[email protected]
4 Business Process 4 [email protected]
This has worked fine as in various stored procedures as they can select a field from this table and use it in the xp_sendmail stored procedure. Emails go out fine and to multiple recipients. The problems come if I want to edit this table. If I want to add or remove an email from the list, I have to perform some string manipulation to ensure that I edit everything correctly. Let's say that I want to remove Delaney from all the lines above. I have to first find all instances of [email protected] and then replace them with spaces. Not the REPLACE function (see Part 3 of this series) will perform this work, but I have two cases here. I have to allow for the email at the end of the email separately from being in the middle. The reason: the semicolons that separate the emails. This is not a huge problem and the REPLACE function will handle most edits that are needed, the other problem is that this storage is fundamentally a poor design. I am using a single field to hold multiple data elements. If I wanted to search on multiple emails, I have problems. If I want to total the number of emails a series of people recieve, I cannot easily do this. Using this storage solution, however, has allowed me to easily send a group of emails into the mail stored procedure without using a cursor to concatenate the data. I can get a list of emails using the following code: declare @r varchar( 255)
select @r = recipients
from email_notification
where process='Business Process 1'However, what I really want to get to is to have the data stored like this:
id process recipients cc
---- -------------------- ----------------------------------------------------- ------
1 Business Process 1 [email protected]
2 Business Process 1 [email protected]
3 Business Process 2 [email protected]
4 Business Process 3 [email protected]
5 Business Process 3 [email protected]
6 Business Process 4 [email protected] How can I do it? Read on... The Solution
I have to admit that I would probably never have stumbled on this technique if I had read about it. That's a good reason to keep reading (hint, hint). I have known for some time that there is a SET operator in T-SQL that can be used to set the value of a variable. I have never bothered to use it, preferring the SELECT operator. I don't have a good reason, I just like the SELECT operator. I had also assumed that these two operators performed the same function and one was kept for backwards compatibility. Then I read about what the differences were in an advanced T-SQL programming book. The SET statement can only assign a single value, where SELECT can assing multiple values. OK, that is interesting, but how does that help me. I don't really want to assign two variables like this: declare @r varchar( 255), @c varchar( 255)
select @r = recipients, @c = cc
from email_notification
where process='Business Process 1'Or even this:
declare @r varchar( 255), @c varchar( 255)
select @r = substring( recipients, 1, charindex( ';', recipients)-1),
@c = substring( recipients, charindex( ';', recipients)+1, 255)
from email_notification
where process='Business Process 1'
select @r, @cIt was then that I kept reading and learned about another feature of the SELECT statemet. I have known that I can assign a single value to a variable even if the query returns mutliple results. For example, look at his code: declare @r varchar( 255)
select @r = recipients
from email_notificationWhat will be the value of @r? It will be the last value returned by the result set. In this case the value will be "[email protected]". So does this help me? Well, T-SQL is incredibly flexible and able to embed it's commands within other commands, much like I used to do in LISP early in my career. The SELECT statement will allow me to add an expression to the query inside the assinment and allow T-SQL to perform my concatenation. Assume my data set now looks like this: id process recipients cc
---- -------------------- ----------------------------------------------------- ------
1 Business Process 1 [email protected]
2 Business Process 1 [email protected]
3 Business Process 2 [email protected]
4 Business Process 3 [email protected]
5 Business Process 3 [email protected]
6 Business Process 4 [email protected] I can run the following:
declare @r varchar( 1000)
select @r = ''
select @r = rtrim(@r) + recipients + ';'
from email_notification
where process = 'business process 1'
select substring( @r, 1, len( @r) - 1)Note that I initialize my variable to a blank string and then allow T-SQL to concatenate my string for me. The result is "[email protected];[email protected]". The last substring is designed to merely remove the final semicolon from the string.
帖子就结了
我也想找日上星