列数据如下:
10*2100*5900-9000
12*3300*11880
16.5*3015*7000
Φ12*9
Φ12
Φ18*9
Φ22*9
∮16*9
∮16
∮20*9
14m/m
¢6.5
¢10
截取列“*”、“/”区间的数据
如区间10到16数据如下:
10*2100*5900-9000
12*3300*11880
Φ12*9
Φ12
∮16*9
∮16
14m/m
¢10
10*2100*5900-9000
12*3300*11880
16.5*3015*7000
Φ12*9
Φ12
Φ18*9
Φ22*9
∮16*9
∮16
∮20*9
14m/m
¢6.5
¢10
截取列“*”、“/”区间的数据
如区间10到16数据如下:
10*2100*5900-9000
12*3300*11880
Φ12*9
Φ12
∮16*9
∮16
14m/m
¢10
INSERT @TB
SELECT '10*2100*5900-9000' UNION ALL
SELECT '12*3300*11880' UNION ALL
SELECT '16.5*3015*7000' UNION ALL
SELECT 'Φ12*9' UNION ALL
SELECT 'Φ12' UNION ALL
SELECT 'Φ18*9' UNION ALL
SELECT 'Φ22*9' UNION ALL
SELECT '∮16*9' UNION ALL
SELECT '∮16' UNION ALL
SELECT '∮20*9' UNION ALL
SELECT '14m/m' UNION ALL
SELECT '¢6.5' UNION ALL
SELECT '¢10'SELECT LEFT(COL, PATINDEX('%[*/]%', COL+'*')-1)
FROM @TB
/*
10
12
16.5
Φ12
Φ12
Φ18
Φ22
∮16
∮16
∮20
14m
¢6.5
¢10*/
select '10*2100*5900-9000' union all
select '12*3300*11880' union all
select '16.5*3015*7000' union all
select 'Φ12*9' union all
select 'Φ12' union all
select 'Φ18*9' union all
select 'Φ22*9' union all
select '∮16*9' union all
select '∮16' union all
select '∮20*9' union all
select '14m/m' union all
select '¢6.5' union all
select '¢10'--select * from tb
select case when charindex('*',coldata)>0 then left(coldata,charindex('*',coldata)-1)
when charindex('/',coldata)>0 then left(coldata,charindex('/',coldata)-1)
else coldata end from tb
drop table tb
case charindex('*',STR)
when 0 then
case charindex('/',STR)
when 0 then STR
else substring(STR,1,charindex('/',STR)-1) end
else substring(STR,1,charindex('*',STR)-1)
end as a
from test2输出结果
CN STR A
1 10*2100*5900-9000 10
2 12*3300*11880 12
3 16.5*3015*7000 16.5
4 Φ12*9 Φ12
5 Φ12 Φ12
6 Φ18*9 Φ18
7 Φ22*9 Φ22
8 ∮16*9 ∮16
9 ∮16 ∮16
10 ∮20*9 ∮20
11 14m/m 14m
12 ¢6.5 ¢6.5
13 ¢10 ¢10 其中A列是你想要的结果
INSERT @TB SELECT '10*2100*5900-9000'
UNION ALL SELECT '12*3300*11880'
UNION ALL SELECT '16.5*3015*7000'
UNION ALL SELECT 'Φ12*9'
UNION ALL SELECT 'Φ12'
UNION ALL SELECT 'Φ18*9'
UNION ALL SELECT 'Φ22*9'
UNION ALL SELECT '∮16*9'
UNION ALL SELECT '∮16'
UNION ALL SELECT '∮20*9'
UNION ALL SELECT '14M/M'
UNION ALL SELECT '¢6.5'
UNION ALL SELECT '¢10'SELECT *
FROM @TB
WHERE CAST(SUBSTRING(LEFT(COL, PATINDEX('%[*/]%', COL+'*')-1),PATINDEX('%[0-9]%', LEFT(COL, PATINDEX('%[*/]%', COL+'*')-1)),LEN(LEFT(COL, PATINDEX('%[*/M]%', COL+'*')-1))) AS FLOAT)
BETWEEN 10 AND 16
/*
COL
--------------------------------------------------
10*2100*5900-9000
12*3300*11880
Φ12*9
Φ12
∮16*9
∮16
14M/M
¢10(8 行受影响)
*/