如
tab
ID Concent
1 广州|510000|020-8222222|呵呵 查询出为
ID city post tel title
1 广州 510000 020-8222222 呵呵
谢谢急
tab
ID Concent
1 广州|510000|020-8222222|呵呵 查询出为
ID city post tel title
1 广州 510000 020-8222222 呵呵
谢谢急
调试欢乐多
GO
CREATE TABLE TB (IDNO VARCHAR(10),Concent VARCHAR(40))
INSERT INTO TB
SELECT 'A010001','广州¦510000¦020-8222222¦呵呵'
UNION ALL SELECT 'A010002','厦门¦123321¦0592-8222222¦近水楼台先得月'SELECT TOP 1000 ID=Identity(int,1,1) into #T from syscolumns a
SELECT
IDNO,
CITY=MAX(CASE LEN(LEFT(Concent,b.ID))-LEN(REPLACE(LEFT(Concent,b.ID),'¦',''))
WHEN 0 THEN SUBSTRING(A.Concent,B.ID,CHARINDEX('¦',A.Concent+'¦',B.ID)-B.ID) ELSE '' END),
POST=MAX(CASE LEN(LEFT(Concent,b.ID))-LEN(REPLACE(LEFT(Concent,b.ID),'¦',''))
WHEN 1 THEN SUBSTRING(A.Concent,B.ID,CHARINDEX('¦',A.Concent+'¦',B.ID)-B.ID) ELSE '' END),
TEL=MAX(CASE LEN(LEFT(Concent,b.ID))-LEN(REPLACE(LEFT(Concent,b.ID),'¦',''))
WHEN 2 THEN SUBSTRING(A.Concent,B.ID,CHARINDEX('¦',A.Concent+'¦',B.ID)-B.ID) ELSE '' END),
TITLE=MAX(CASE LEN(LEFT(Concent,b.ID))-LEN(REPLACE(LEFT(Concent,b.ID),'¦',''))
WHEN 3 THEN SUBSTRING(A.Concent,B.ID,CHARINDEX('¦',A.Concent+'¦',B.ID)-B.ID) ELSE '' END)
FROM TB A,#T B
WHERE CHARINDEX('¦','¦'+A.Concent,b.ID)=b.ID
GROUP BY IDNO/*
IDNO CITY POST TEL TITLE
---------- --------- ------------- ------------- --------
A010001 广州 510000 020-8222222 呵呵
A010002 厦门 123321 0592-8222222 近水楼台先得月(所影响的行数为 2 行)
*/
http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html
IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000), --包含多个数据项的字符串
@INDEX int, --要获取的数据项的位置
@SPLIT varchar(10) --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
declare @tab table([ID] int,[Concent] nvarchar(24))
Insert @tab
select 1,N'广州¦510000¦020-8222222¦呵呵'
Select [ID],
DBO.SPLIT_STR([Concent],1,'¦') city,
DBO.SPLIT_STR([Concent],2,'¦') post,
DBO.SPLIT_STR([Concent],3,'¦') tel,
DBO.SPLIT_STR([Concent],4,'¦') title
from @tab
/*
ID city post tel title
----------- ---------- ---------- -------------------- ----------
1 广州 510000 020-8222222 呵呵
*/