运行试试 CREATE TABLE #TEMP_LIST ( CLOUM NVARCHAR(50) ) CREATE TABLE #OBJ_LIST ( CLOUM NVARCHAR(10) ) --******************用';'分隔的数组组成的表 DECLARE @STR NVARCHAR(50), @NewStr1 NVARCHAR(50), @PRI NVARCHAR(1), @INDEX INT SET @STR = '1-2-33-444-55;66-77-9999-10000' SET @PRI = ';' SET @INDEX = 0 WHILE(CHARINDEX(@PRI, @STR, @INDEX)>0) BEGIN SET @INDEX = CHARINDEX(@PRI, @STR, @INDEX) SET @NewStr1 = LEFT(@STR, @INDEX-1) INSERT INTO #TEMP_LIST VALUES (@NewStr1) SET @STR = RIGHT(@STR, LEN(@STR)-@INDEX) END INSERT INTO #TEMP_LIST VALUES (@STR) SELECT * FROM #TEMP_LIST--******************从结果表中获取所要的字段 DECLARE @OBJ_LIST NVARCHAR(10), @ID INT SET @INDEX = 0 SET @PRI = '-' SET @ID = 1 DECLARE OBJ_CUR CURSOR FOR SELECT CLOUM FROM #TEMP_LIST --游标 OPEN OBJ_CUR DECLARE @OBJ NVARCHAR(50) FETCH NEXT FROM OBJ_CUR INTO @OBJ WHILE @@FETCH_STATUS = 0 BEGIN SET @INDEX = CHARINDEX(@PRI, @OBJ, @INDEX) INSERT INTO #OBJ_LIST VALUES(LEFT(@OBJ, @INDEX-1)) FETCH NEXT FROM OBJ_CUR INTO @OBJ END CLOSE OBJ_CUR SELECT * FROM #OBJ_LISTDROP TABLE #OBJ_LIST DROP TABLE #TEMP_LIST
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
+----------------------------------------+
| substring_index('1-2-33-444-55','-',1) |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)mysql> select concat(substring_index(@temp,'-',1),',',substring_index(substring_
index(@temp,';',-1),'-',1)) as A;
+------+
| A |
+------+
| 1,66 |
+------+
1 row in set (0.00 sec)
比较短而且有规律的话就用SUBSTRING_INDEX(str,delim,count) 函数,
楼上第一个是用“-”来做为分隔符的,第二个是用“;”来做分隔符的
如果字符串再长些就不好写sql了,还是用程序好~~分割后想去第几个就取第几个
运行试试
CREATE TABLE #TEMP_LIST
(
CLOUM NVARCHAR(50)
)
CREATE TABLE #OBJ_LIST
(
CLOUM NVARCHAR(10)
)
--******************用';'分隔的数组组成的表
DECLARE @STR NVARCHAR(50), @NewStr1 NVARCHAR(50), @PRI NVARCHAR(1), @INDEX INT
SET @STR = '1-2-33-444-55;66-77-9999-10000'
SET @PRI = ';'
SET @INDEX = 0
WHILE(CHARINDEX(@PRI, @STR, @INDEX)>0)
BEGIN
SET @INDEX = CHARINDEX(@PRI, @STR, @INDEX)
SET @NewStr1 = LEFT(@STR, @INDEX-1)
INSERT INTO #TEMP_LIST VALUES (@NewStr1)
SET @STR = RIGHT(@STR, LEN(@STR)-@INDEX)
END
INSERT INTO #TEMP_LIST VALUES (@STR)
SELECT * FROM #TEMP_LIST--******************从结果表中获取所要的字段
DECLARE @OBJ_LIST NVARCHAR(10), @ID INT
SET @INDEX = 0
SET @PRI = '-'
SET @ID = 1
DECLARE OBJ_CUR CURSOR FOR SELECT CLOUM FROM #TEMP_LIST --游标
OPEN OBJ_CUR
DECLARE @OBJ NVARCHAR(50)
FETCH NEXT FROM OBJ_CUR INTO @OBJ
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INDEX = CHARINDEX(@PRI, @OBJ, @INDEX)
INSERT INTO #OBJ_LIST VALUES(LEFT(@OBJ, @INDEX-1))
FETCH NEXT FROM OBJ_CUR INTO @OBJ
END
CLOSE OBJ_CUR
SELECT * FROM #OBJ_LISTDROP TABLE #OBJ_LIST
DROP TABLE #TEMP_LIST
而且是不是数据库中根本就不应改一个字段存这么多的数据