比如 a表
name
电脑,彩电,冰箱,洗衣机要变成
1 电脑
2 彩电
3 冰箱
4 洗衣机
name
电脑,彩电,冰箱,洗衣机要变成
1 电脑
2 彩电
3 冰箱
4 洗衣机
解决方案 »
- 查询本次采购和上次采购价格,没有则不显示(升级版)
- Sql循环插入的问题
- sql 2005 如何进行差异备份及差异备份如何还原????
- 急!!!!求助一个简单的SQL语句
- 关于导入导出问题
- 有难度阿!如何将15位身份证升级成18位?
- SQL如何按日期判断一列值中的唯一值
- 关于SQLServer6.5的数据恢复的问题,请帮忙
- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]用户
- 谁来挑战这个顶级的数据处理???????????
- 多行的条件查询语句如何实现?
- SQL Server 自治事务(CLR 实现)
)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))
AS
BEGIN
/* Function body */
DECLARE @pos int
SET @pos = CHARINDEX(',', @str)
WHILE @pos > 0
BEGIN
INSERT @r(value) VALUES(LEFT(@str, @pos - 1))
SELECT
@str = STUFF(@str, 1, @pos, ''),
@pos = CHARINDEX(',', @str)
END
IF @str > ''
INSERT @r(value) VALUES(@str)
RETURNENDselect f_splitstr(name) from tb
returns @temp table (a nvarchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
endDECLARE @STR NVARCHAR(30)SET @STR=N'电脑,彩电,冰箱,洗衣机'
SELECT * FROM [dbo].[fn_split](@STR,',')
returns @temp table (a nvarchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
endDECLARE @STR NVARCHAR(30)SET @STR=N'电脑,彩电,冰箱,洗衣机'
SELECT * FROM [dbo].[fn_split](@STR,',')a
-------------
电脑
彩电
冰箱
洗衣机(4 row(s) affected)
select parsename(replace(name,',','.'),4)
from a
union
select parsename(replace(name,',','.'),3)
from a
union
select parsename(replace(name,',','.'),2)
from a
union
select parsename(replace(name,',','.'),1)
from a
from @x.nodes('/root/row') as r(row)
----------
电脑
彩电
冰箱
洗衣机(所影响的行数为 4 行)
SET @var = '电脑,彩电,冰箱,洗衣机,'DECLARE @Tb TABLE (ID bigint IDENTITY(1,1),Name varchar(6))WHILE (charindex(',',@var) > 0 )
BEGIN
INSERT @Tb(Name) VALUES(substring(@var,1,charindex(',',@var)-1))
SET @var = stuff(@var,1,charindex(',',@var),'')
ENDSELECT * FROM @Tb
ID Name
-------------------- ------
1 电脑
2 彩电
3 冰箱
4 洗衣机(4 行受影响)
declare @sql varchar(100)
set @sql = '电脑,彩电,冰箱,洗衣机'
set @sql = 'select '''+replace(@sql,',',''' union all select ''')+''''
print @sql
exec (@sql)
from @x.nodes('/root/row') as r(row)
id v
-------------------- ----------
1 电脑
2 彩电
3 冰箱
4 洗衣机(所影响的行数为 4 行)
改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B