日期:2014-05-18  浏览次数:20493 次

sql 如何根据指定分隔符号输出字符串?
这里分隔符号是 ‘-’,我只是想提取字符串里面的第一组数字和最后一组数字,字符串里面分割符号的数量不定,有可能3个,也有可能很多个

原始数据:
id name
1 12-32A-DDD
2 32-22F-FDD-SD
3 1-32K-F-S-DD-Q-10

希望的输出结果是:

id name name2
1 12 DDD
2 32 SD
3 1 10


------解决方案--------------------
substring函数
------解决方案--------------------
SQL code
select id,name=left(name,len(name)-charindex('-',name)),
          name2=REVERSE(left(REVERSE(name),len(name)-charindex('-',REVERSE(name))))
from tb

------解决方案--------------------
SQL code
select
 id,
 name=left(name,len(name)-charindex('-',name)),
 name2=REVERSE(left(REVERSE(name),len(name)-charindex('-',REVERSE(name))))
from
 tb

------解决方案--------------------
SQL code

declare @t table(
id int
,name varchar(30)
);
insert into @t
SELECT 1, '12-32A-DDD' UNION ALL
select 2, '32-22F-FDD-SD' UNION ALL
select 3, '1-32K-F-S-DD-Q-10'
;

WITH t1 AS
(
SELECT ID, Name+'-' as name
FROM @t
),
t2 as
(
SELECT ID, CONVERT(VARCHAR(30),LEFT(name,CHARINDEX('-',name)-1)) AS Subname 
    ,STUFF(name, 1, CHARINDEX('-',name), '') AS name, 1 AS Level
FROM t1
UNION ALL
SELECT t1.ID, CONVERT(VARCHAR(30),LEFT(t2.name,CHARINDEX('-',t2.name)-1))
    ,STUFF(t2.name, 1, CHARINDEX('-',t2.name), ''), t2.Level+1
FROM t2 JOIN
     t1 ON t2.ID=t1.ID and t2.name>''
)
SELECT ID
, MIN(CASE WHEN name>'' then subname else Null end) As name
, MIN(CASE WHEN name='' then subname else Null end) As name2
FROM t2
where level=1 or name=''
group by ID
/*
(3 行受影响)
ID          name                           name2
----------- ------------------------------ ------------------------------
1           12                             DDD
2           32                             SD
3           1                              10
警告: 聚合或其他 SET 操作消除了空值。
*/