如何在字符型字段中提取出其中的数字部分

Question

;WITH CTE AS
(
SELECT pm,zbz,stuff(zbz,patindex('%[^0-9]%',zbz),1,'') AS QTY FROM #s
UNION ALL
SELECT pm,zbz,stuff(QTY,patindex('%[^0-9]%',QTY),1,'') FROM CTE WHERE patindex('%[^0-9]%',QTY)>0
)
SELECT DISTINCT * FROM CTE WHERE patindex('%[^0-9]%',QTY)=0

Answer 1

select
  *,
  值=left(replace(zbz,'*',''),patindex('%[^0-9]%',replace(zbz,'*',''))-1)
from 
  #s/**
pm                                       zbz                  值                      
---------------------------------------- -------------------- ------------------------
面包                                       *100mg*              100
面包                                       *100支/盒*             100
面包                                       *4支/                 4
面包                                       100支/盒               100
面包                                       10瓶                  10
面包                                       10支                  10
面包                                       20支/包                20
面包                                       5盒                   5（所影响的行数为 8 行）
**/

Answer 2

[Quote=引用 2 楼 josy 的回复:]
SQL codeselect
  *,
  值=left(replace(zbz,'*',''),patindex('%[^0-9]%',replace(zbz,'*',''))-1)
from 
  #s执行 大数据量的时候 报错
服务器: 消息 536，级别 16，状态 3，行 1
向 substring 函数传递了无效的 length 参数。

Answer 3

如果字段值中存在 '5'、'10'  这样 本身就是数字的 就会出现错误
如果在#S表中 增加 以下2列
insert into #s
(pm,zbz)
select '面包','5'
union
select '面包',''再执行
select
  *,
  值=left(replace(zbz,'*',''),patindex('%[^0-9]%',replace(zbz,'*',''))-1)
from 
  #s
就会 报错 

Answer 4

select
  *,
  值=left(replace(zbz,'*',''),patindex('%[^0-9]%',replace(zbz,'*','')+'*')-1)
from 
  #s