22,210
社区成员
发帖
与我相关
我的任务
分享
/*添加一个拆分字符串函数
CREATE FUNCTION [dbo].[fn_split] ( @String NVARCHAR(MAX) ,
@Str NVARCHAR(20),
@distinct BIT )
RETURNS @table TABLE ( Value NVARCHAR(MAX),orders int)
AS
BEGIN
DECLARE @i INT ,
@j INT ,
@l INT ,
@v NVARCHAR(MAX),
@orders int
SET @i = 0
SET @j = 0
SET @l = LEN(@String)
set @orders=1
WHILE ( @j < @l )
BEGIN
SET @j = CHARINDEX(@Str, @String, @i + 1)
IF @j = 0
SET @j = @l + 1
SET @v = SUBSTRING(@String, @i + 1, @j - @i - 1 )
IF @v <> '' AND (@distinct = 0 OR NOT EXISTS (SELECT 1 FROM @table WHERE Value = @v))
INSERT INTO @table
VALUES ( LTRIM(RTRIM(@v)),@orders)
SET @i = @j + LEN(@Str) - 1
set @orders=@orders+1
END
RETURN
END
*/
--正文
DECLARE @str NVARCHAR(MAX)
SET @str=N'1、[守时]按时到校,不准迟到、早退和旷课
2、[夜晚]晚上就寝铃响后不准在校园内逗留
3、[骑车]骑自行车进出校门必须下车
4、[文明]不准在校园内追逐打闹,不得有说脏话、骂人、吵架等不文明行为
5、[打架]严禁打架斗殴,更不准纠集校外人员来闹事。严禁偷盗、敲诈等违法行为'
SELECT
(SELECT CHAR(10)+CAST(ROW_NUMBER() OVER(ORDER BY orders) AS NVARCHAR)+RIGHT([Value],LEN([Value])-1)
FROM dbo.fn_split(@str,CHAR(10),1) --如果是回车就换成CHAR(13)
WHERE [Value] NOT LIKE '%[文明]%' FOR XML PATH ('')
,TYPE).value('.','NVARCHAR(MAX)') AS new_str
应该满足LZ了with t1(id, c1) as(
Select '123',N'1、[守时]按时到校,不准迟到、早退和旷课
2、[夜晚]晚上就寝铃响后不准在校园内逗留
3、[骑车]骑自行车进出校门必须下车
4、[文明]不准在校园内追逐打闹,不得有说脏话、骂人、吵架等不文明行为
5、[打架]严禁打架斗殴,更不准纠集校外人员来闹事。严禁偷盗、敲诈等违法行为'
)
, t2(id, c1) as(
SELECT a.id,b.c1 FROM
( SELECT id,[value] = CONVERT(XML , '<v>' + REPLACE(c1 , CHAR(10) , '</v><v>') + '</v>') from t1) A
OUTER APPLY
( SELECT c1 = N.v.value('.' , 'varchar(4000)') FROM A.[value].nodes('/v') N ( v )) B
)
, t3(id, c1) as (
select * from t2 where c1 not like '%[文明]%'
)
select id,stuff(
(select CHAR(10) + convert(varchar(4000),c1) from t3 where id=A.id order by id for xml path('')
),1,1,'') as c1
from t3 A
group by id
with t1(id, c1) as(
Select '123',N'1、[守时]按时到校,不准迟到、早退和旷课
2、[夜晚]晚上就寝铃响后不准在校园内逗留
3、[骑车]骑自行车进出校门必须下车
4、[文明]不准在校园内追逐打闹,不得有说脏话、骂人、吵架等不文明行为
5、[打架]严禁打架斗殴,更不准纠集校外人员来闹事。严禁偷盗、敲诈等违法行为'
)
, t2(id, c1) as(
SELECT a.id,b.c1 FROM
( SELECT id,[value] = CONVERT(XML , '<v>' + REPLACE(c1 , CHAR(10) , '</v><v>') + '</v>') from t1) A
OUTER APPLY
( SELECT c1 = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )) B
)
, t3(id, c1) as (
select * from t2 where c1 not like '%[文明]%'
)
select id,stuff(
(select CHAR(10) + convert(varchar,c1) from t3 where id=A.id order by id for xml path('')
),1,1,'') as c1
from t3 A
group by id
USE tempdb
GO
---------------------------- 1. 测试表及测试数据 --------------------------------------
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
create table test (
id char(5) not null,
content varchar(2000) null,
constraint PK_TEST primary key (id)
)
INSERT INTO test(id,CONTENT)
SELECT 1,'1、[守时]按时到校
2、[夜晚]晚上就寝铃响
3、[骑车]骑自行车进出
4、[文明]不准在校园内
5、[打架]严禁打架斗殴' union all
SELECT 2,'1、[上车]123
2、[文明]不准在校园内追逐
3、[打架]xxx'
GO
---------------------------- 2. 增加按行分隔表值函数 -------------------------------------
IF OBJECT_ID('dbo.Fun_SplitByLine') IS NOT NULL
DROP FUNCTION dbo.Fun_SplitByLine
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-04-26
-- Description: 按行分割表值函数
-- =============================================
CREATE FUNCTION dbo.Fun_SplitByLine
(
@str NVARCHAR(MAX)
)
RETURNS
@t TABLE
(
rowNum INT,
line NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @i INT
SET @i=1
WHILE CHARINDEX(CHAR(10),@str)>0
BEGIN
INSERT INTO @t(rowNum,line)
SELECT @i,SUBSTRING(@str,1,CHARINDEX(char(10),@str))
SET @str = SUBSTRING(@str,CHARINDEX(char(10),@str)+1,LEN(@str))
SET @i = @i+1
END
IF LEN(@str)>0
BEGIN
INSERT INTO @t(rowNum,line) VALUES(@i,@str)
END
UPDATE @t SET line=REPLACE(REPLACE(line,char(13),''),CHAR(10),'')
RETURN
END
GO
---------------------------- 3. 输出结果 -------------------------------------
SELECT *,(
SELECT replace( STUFF(
(
SELECT ';'+fs.line
FROM test a CROSS APPLY dbo.fun_splitbyline(a.[content]) fs
WHERE
a.id=t.id
AND fs.line NOT LIKE '%文明%' for xml PATH('')
)
,1,1,''
),';','
')) AS result
FROM test t
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[content] nvarchar(56))
Insert #T
select 1,N'[守时]按时到校,不准迟到、早退和旷课' union all
select 2,N'[夜晚]晚上就寝铃响后不准在校园内逗留' union all
select 3,N'[骑车]骑自行车进出校门必须下车' union all
select 4,N'[文明]不准在校园内追逐打闹,不得有说脏话、骂人、吵架等不文明行为' union all
select 5,N'[打架]严禁打架斗殴,更不准纠集校外人员来闹事。严禁偷盗、敲诈等违法行为'
Go
--测试数据结束
UPDATE #T SET content = '文明' WHERE content LIKE '%文明%'
Select * from #T
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
create table #Tmp_Data(id char(5),
content nvarchar(2000),
constraint PK_TEST primary key (id))
go
INSERT INTO #Tmp_Data
Select '123',N'1、[守时]按时到校,不准迟到、早退和旷课
2、[夜晚]晚上就寝铃响后不准在校园内逗留
3、[骑车]骑自行车进出校门必须下车
4、[文明]不准在校园内追逐打闹,不得有说脏话、骂人、吵架等不文明行为
5、[打架]严禁打架斗殴,更不准纠集校外人员来闹事。严禁偷盗、敲诈等违法行为'
UPDATE #Tmp_Data SET Content=REPLACE(content,'文明','')
Select * From #Tmp_Data