CREATE TABLE TEST(COL NVARCHAR(20)) INSERT TEST SELECT '¢160*0.4*8' INSERT TEST SELECT '¢963333*0.4*8' INSERT TEST SELECT '¢160*0.410' --SELECT * FROM TEST GO-- 如何从¢160*0.4*8等类似的字符中提取出第二个*左边的字符 CREATE FUNCTION FG_TEST(@COL NVARCHAR(20)) RETURNS NVARCHAR(20) AS BEGIN DECLARE @STR NVARCHAR(20) DECLARE @RESULT NVARCHAR(20) IF CHARINDEX('*',@COL)>0 BEGIN BEGIN SET @STR=RIGHT(@COL,LEN(@COL)-CHARINDEX('*',@COL)) SET @RESULT=LEFT(@COL,CHARINDEX('*',@COL)) END IF CHARINDEX('*',@STR)>0 BEGIN SET @RESULT=@RESULT+LEFT(@STR,CHARINDEX('*',@STR)-1) END ELSE SET @RESULT='' --RETURN CHARINDEX('*',@COL) END ELSE SET @RESULT='' RETURN @RESULT END GOSELECT *,DBO.FG_TEST(COL) AS RESULT FROM TESTDROP FUNCTION FG_TEST DROP TABLE TEST
DECLARE @S VARCHAR(2000) SET @S='¢160*0.4*8' SELECT LEFT(@S,CHARINDEX('*',STUFF(@S,CHARINDEX('*',@S),1,''),CHARINDEX('*',@S)))
INSERT TEST SELECT '¢160*0.4*8'
INSERT TEST SELECT '¢963333*0.4*8'
INSERT TEST SELECT '¢160*0.410'
--SELECT * FROM TEST
GO-- 如何从¢160*0.4*8等类似的字符中提取出第二个*左边的字符
CREATE FUNCTION FG_TEST(@COL NVARCHAR(20))
RETURNS NVARCHAR(20)
AS
BEGIN
DECLARE @STR NVARCHAR(20)
DECLARE @RESULT NVARCHAR(20) IF CHARINDEX('*',@COL)>0
BEGIN
BEGIN
SET @STR=RIGHT(@COL,LEN(@COL)-CHARINDEX('*',@COL))
SET @RESULT=LEFT(@COL,CHARINDEX('*',@COL))
END IF CHARINDEX('*',@STR)>0
BEGIN
SET @RESULT=@RESULT+LEFT(@STR,CHARINDEX('*',@STR)-1)
END
ELSE
SET @RESULT=''
--RETURN CHARINDEX('*',@COL)
END
ELSE
SET @RESULT='' RETURN @RESULT
END
GOSELECT *,DBO.FG_TEST(COL) AS RESULT FROM TESTDROP FUNCTION FG_TEST
DROP TABLE TEST
SET @S='¢160*0.4*8'
SELECT LEFT(@S,CHARINDEX('*',STUFF(@S,CHARINDEX('*',@S),1,''),CHARINDEX('*',@S)))