22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(28))
Insert #T
select N'98%羊绒' union all
select N'98.5%山羊绒' union all
select N'99.1%山羊绒' union all
select N'灰色, size small 98.5%山羊绒' union all
select N'灰色, size small 89.5%山羊绒'
Go
--测试数据结束
SELECT *
FROM #T
WHERE col LIKE '%绒%'
AND CONVERT(FLOAT, SUBSTRING([col],
PATINDEX('%[^0-9|.][0-9|.]%', [col]) + 1,
PATINDEX('%[0-9][^0-9|.]%', [col])
- PATINDEX('%[^0-9|.][0-9]%', [col]))) > 90
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(28))
Insert #T
select N'98%羊绒' union all
select N'98.5%山羊绒' union all
select N'99.1%山羊绒' union all
select N'89.1%山羊绒'
Go
--测试数据结束
SELECT *
FROM #T
WHERE CONVERT(FLOAT, SUBSTRING([col], 0, charindex('%', [col]))) > 90
AND col LIKE '%绒%'
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(28))
Insert #T
select N'98%羊绒' union all
select N'98.5%山羊绒' union all
select N'99.1%山羊绒' union all
select N'89.1%山羊绒'
Go
--测试数据结束
SELECT *
FROM #T
WHERE CONVERT(FLOAT, SUBSTRING([col], 0,
PATINDEX('%[0-9][^0-9|.]%', [col]) + 1)) > 90
AND col LIKE '%绒%'