SQL 从字符串中取数字

Question

DTY-100D/36FFDY-48D/100FDTY-100dtex/48FDTY-36D/148F类似这种字符串。我想把字符串里面的 100，48，100，36 分别取出来，该怎么写，写了很久都没能成功~

Answer 1

比如第一个，DTY-100D/36F 我只要取出100

Answer 2

select left(stuff(xx,1,4,''),charindex('D',stuff(xx,1,4,'')-1)

Answer 3

 SELECT left(stuff(FModel,1,4,''),charindex('D',stuff(FModel,1,4,'')-1)  FROM t_ICItem WHERE (FModel LIKE '%y%')错误提示：From附近有语法错误

Answer 4

if not object_id('tb') is null
drop table tb
Go
Create table tb([col] nvarchar(15))
Insert tb
select N'DTY-100D/36F' union all
select N'FDY-48D/100F' union all
select N'DTY-100dtex/48F' union all
select N'DTY-36D/148F'
Go
Select left(stuff(col,1,patindex('%[0-9]%',col)-1,''),patindex('%[^0-9]%',stuff(col,1,patindex('%[0-9]%',col)-1,''))-1)
from tb
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100
48
100
36*/

Answer 5

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-03-15 15:39:05
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#A
if object_id('tempdb.dbo.#A') is not null drop table #A
go 
create table #A([A] varchar(15))
insert #A
select 'DTY-100D/36F' union all
select 'FDY-48D/100F' union all
select 'DTY-100dtex/48F' union all
select 'DTY-36D/148F'
--------------开始查询--------------------------select SUBSTRING(
SUBSTRING(A,PATINDEX('%[0-9]%',A),CHARINDEX('/',A)-PATINDEX('%[0-9]%',A)),1,
PATINDEX('%[^0-9]%',SUBSTRING(A,PATINDEX('%[0-9]%',A),CHARINDEX('/',A)-PATINDEX('%[0-9]%',A)))-1) from #A
----------------结果----------------------------
/* （所影响的行数为 4 行）                
--------------- 
100
48
100
36（所影响的行数为 4 行）
*/

Answer 6

if not object_id('tb') is null
    drop table tb
Go
Create table tb([col] nvarchar(15))
Insert tb
select N'DTY-100D/36F' union all
select N'FDY-48D/100F' union all
select N'DTY-100dtex/48F' union all
select N'DTY-36D/148F'select left(stuff([col],1,4,''),patindex('%[a-z]%',stuff([col],1,4,''))-1) from TB/*------------
100
48
100
36(4 行受影响)*/drop table TB