有下面一组数据:
3YG32*0.2*120
4XG32*0.5*230轮轴
3YX45*190
.....期望结果:
120
230
190
...即把最后一个*后面的数字取出来,现在问题是有的数字后面带汉字,如“4XG32*0.5*230轮轴”,有什么好方法呢?
3YG32*0.2*120
4XG32*0.5*230轮轴
3YX45*190
.....期望结果:
120
230
190
...即把最后一个*后面的数字取出来,现在问题是有的数字后面带汉字,如“4XG32*0.5*230轮轴”,有什么好方法呢?
IF OBJECT_ID('dbo.fn_china_word') IS NOT NULL
DROP FUNCTION dbo.fn_china_word
GO
CREATE FUNCTION dbo.fn_china_word(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[吖-座]%',@S) > 0---去掉 ^ 就是取非汉字
SET @S = STUFF(@S,PATINDEX('%[吖-座]%',@S),1,N'')
RETURN @S
END
GO
----------------------------------------
declare @tab table(col nvarchar(30))
insert into @tab
select '3YG32*0.2*120' union all
select '4XG32*0.5*230轮轴' union all
select '3YX45*190'select dbo.fn_china_word(reverse(substring(reverse(col),1,charindex('*',reverse(col))-1))) from @tab
/*
----------------------------------------------------------------------------------------------------
120
230
190
*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] VARCHAR(17))
INSERT [tb]
SELECT '3YG32*0.2*120' UNION ALL
SELECT '4XG32*0.5*230轮轴' UNION ALL
SELECT '3YX45*190'
--------------开始查询--------------------------SELECT [name]=REVERSE(SUBSTRING(REVERSE([name]) , PATINDEX('%[0-9]%' , REVERSE([name])) , CHARINDEX('*' , REVERSE([name]))-PATINDEX('%[0-9]%' , REVERSE([name]))))
FROM [tb]
----------------结果----------------------------
/*
[name]
-----------------
120
230
190(3 行受影响)*/