34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t table(col nvarchar(100))
insert @t select N'2009/11/5 22:42:370 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:105 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:194 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:217 猫(100001)
还没搞好?'
SELECT
SUBSTRING(COL,1,CHARINDEX(' ',COL,12)-1)AS 时间,
SUBSTRING(COL,CHARINDEX(' ',COL,12)+1,CHARINDEX('(',COL)-CHARINDEX(' ',COL,12)-1)AS 网名,
SUBSTRING(COL,CHARINDEX('(',COL,12)+1,CHARINDEX(')',COL)-CHARINDEX('(',COL,12)-1)AS QQ号,
REPLACE(SUBSTRING(COL,CHARINDEX(')',COL)+1,LEN(COL)-CHARINDEX(')',COL,12)),CHAR(13)+CHAR(10),'')AS 内容
FROM @T
时间 网名 QQ号 内容
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:370 猫 100001 高人
2009/11/5 22:43:105 风 100002 不对,这样线没有了
2009/11/5 22:43:194 风 100002 [表情]
2009/11/5 22:43:217 猫 100001 还没搞好?
(所影响的行数为 4 行)
declare @t table(col nvarchar(100))
insert @t select N'2009/11/5 22:42:37 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:10 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:19 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:27 猫(100001)
还没搞好?'
SELECT
SUBSTRING(COL,1,CHARINDEX(' ',COL,12)-1)AS 时间,
SUBSTRING(COL,CHARINDEX(' ',COL,12)+1,CHARINDEX('(',COL)-CHARINDEX(' ',COL,12)-1)AS 网名,
SUBSTRING(COL,CHARINDEX('(',COL,12)+1,CHARINDEX(')',COL)-CHARINDEX('(',COL,12)-1)AS QQ号,
REPLACE(SUBSTRING(COL,CHARINDEX(')',COL)+1,LEN(COL)-CHARINDEX(')',COL,12)),CHAR(13)+CHAR(10),'')AS 内容
FROM @T
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
时间 网名 QQ号 内容
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:37 猫 100001 高人
2009/11/5 22:43:10 风 100002 不对,这样线没有了
2009/11/5 22:43:19 风 100002 [表情]
2009/11/5 22:43:27 猫 100001 还没搞好?
(所影响的行数为 4 行)
declare @t table(col nvarchar(200))
insert @t select N'2009/11/5 22:42:37 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:10 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:19 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:27 猫(100001)
还没搞好?'
select
left(col,18)日期,
substring(col,charindex(' ',col,charindex(' ',col)+1),charindex('(',col)-charindex(' ',col,charindex(' ',col)+1))網名,
substring(col,charindex('(',col)+1,charindex(')',col)-charindex('(',col)-1)QQ號,
ltrim(replace(replace(right(col,len(col)-charindex(')',col)),char(13),''),char(10),''))內容
from @t
/*
日期 網名 QQ號 內容
------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:37 猫 100001 高人
2009/11/5 22:43:10 风 100002 不对,这样线没有了
2009/11/5 22:43:19 风 100002 [表情]
2009/11/5 22:43:27 猫 100001 还没搞好?
(4 個資料列受到影響)
*/
declare @t table(col nvarchar(100))
insert @t select N'2009/11/5 22:42:37 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:10 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:19 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:27 猫(100001)
还没搞好?'
SELECT
SUBSTRING(COL,1,CHARINDEX(' ',COL,12)-1)AS 时间,
SUBSTRING(COL,CHARINDEX(' ',COL,12)+1,CHARINDEX('(',COL)-CHARINDEX(' ',COL,12)-1)AS 网名,
SUBSTRING(COL,CHARINDEX('(',COL,12)+1,CHARINDEX(')',COL)-CHARINDEX('(',COL,12)-1)AS QQ号,
REPLACE(SUBSTRING(COL,CHARINDEX(')',COL)+1,LEN(COL)-CHARINDEX(')',COL,12)-1),CHAR(13),'')AS 内容
FROM @T
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
时间 网名 QQ号 内容
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:37 猫 100001
高
2009/11/5 22:43:10 风 100002
不对,这样线没有
2009/11/5 22:43:19 风 100002
[表情
2009/11/5 22:43:27 猫 100001
还没搞好
(所影响的行数为 4 行)
select substring(col,1,19) as 时间,
substring(col,charindex('(',col),len(col)-charindex('(',col)+1 ) as QQ号,
substring(col,19,charindex('(',col)-19) as 网名,
col
from (
select '2009/11/5 22:42:37 小猫(100001)' as col union all
select '2009/11/5 22:43:10 风(100002)' )
A
==============
时间 QQ号 网名
2009/11/5 22:42:37 (100001) 小猫 2009/11/5 22:42:37 小猫(100001)
2009/11/5 22:43:10 (100002) 风 2009/11/5 22:43:10 风(100002)
declare @t table(col nvarchar(200))
insert @t select N'2009/11/5 22:42:37 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:10 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:19 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:27 猫(100001)
还没搞好?'
select left(col,18)日期,
substring(col,charindex(' ',col,charindex(' ',col)+1),charindex('(',col)-charindex(' ',col,charindex(' ',col)+1))網名,
substring(col,charindex('(',col)+1,charindex(')',col)-charindex('(',col)-1)QQ號,
right(col,len(col)-charindex(')',col))內容
from @t
/*
日期 網名 QQ號 內容
------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:37 猫 100001
高人
2009/11/5 22:43:10 风 100002
不对,这样线没有了
2009/11/5 22:43:19 风 100002
[表情]
2009/11/5 22:43:27 猫 100001
还没搞好?
(4 個資料列受到影響)
*/
select substring(col,1,19),
substring(col,charindex('(',col),len(col)-charindex('(',col)+1 ) ,
substring(col,19,charindex('(',col)-19) ,
col
from (
select '2009/11/5 22:42:37 小猫(100001)' as col union all
select '2009/11/5 22:43:10 风(100002)' )
A
declare @t table(col nvarchar(200))
insert @t select N'2009/11/5 22:42:37 猫(100001)
高人'
insert @t select N'2009/11/5 22:43:10 风(100002)
不对,这样线没有了'
insert @t select N'2009/11/5 22:43:19 风(100002)
[表情]'
insert @t select N'2009/11/5 22:43:27 猫(100001)
还没搞好?'
select left(col,18)日期,
substring(col,charindex(' ',col),charindex('(',col)-charindex(' ',col))網名,
substring(col,charindex('(',col)+1,charindex(')',col)-charindex('(',col)-1)QQ號,
right(col,len(col)-charindex(')',col))內容
from @t
/*
日期 網名 QQ號 內容
------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009/11/5 22:42:37 22:42:37 猫 100001
高人
2009/11/5 22:43:10 22:43:10 风 100002
不对,这样线没有了
2009/11/5 22:43:19 22:43:19 风 100002
[表情]
2009/11/5 22:43:27 22:43:27 猫 100001
还没搞好?
(4 個資料列受到影響)
*/