34,590
社区成员
发帖
与我相关
我的任务
分享
WITH test(s) AS (
SELECT '3' UNION ALL
SELECT '3*1' UNION ALL
SELECT '3*1*2' UNION ALL
SELECT '31' UNION ALL
SELECT '31*3' UNION ALL
SELECT '31*3*4'
)
--求出*的个数,补上 2-个数 次 '*0'
SELECT s + REPLICATE('*0',2-(LEN(s) - LEN(REPLACE(s,'*','')))) filled
FROM test
filled
----------
3*0*0
3*1*0
3*1*2
31*0*0
31*3*0
31*3*4
USE tempdb
GO
DECLARE @T TABLE(Col1 VARCHAR(10))
INSERT @T
( Col1 )
VALUES ( '32') ,('32*1')
SELECT CASE LEN(Col1) - LEN(REPLACE(Col1, '*', ''))
WHEN 0 THEN Col1+'*0*0'
WHEN 1 THEN Col1+'*0'
ELSE Col1 END AS Col1
FROM @T
/*
Col1
32*0*0
32*1*0
*/
DECLARE @STR VARCHAR(50)
SET @STR='399'
--SET @STR='399*3'
--SET @STR='399*3*9'
SELECT
CASE
WHEN CHARINDEX('*',@STR)=0 THEN @STR+'*0*0'
WHEN CHARINDEX('*',right(@STR,LEN(@STR)-CHARINDEX('*',@STR)))=0 THEN @STR+'*0'
ELSE @STR
END
ALTER FUNCTION FUN(@STR VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @C INT,@INDEX INT,@INDEX2 INT
SET @C=1
SET @INDEX=CHARINDEX('*',@STR)
WHILE @C<=2
BEGIN
IF @INDEX=0
BEGIN
SET @STR=@STR+'*0'
SET @INDEX2=LEN(@STR)
END
ELSE
SET @INDEX2=@INDEX
SET @INDEX=CHARINDEX('*',@STR,@INDEX2+1)
SET @C=@C+1
END
RETURN @STR
END
更正一下~~CREATE FUNCTION FUN(@STR VARCHAR(10))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @C INT,@INDEX INT
SET @C=1
SET @INDEX=CHARINDEX('*',@STR)
WHILE @C<=3
BEGIN
IF @INDEX=0
SET @STR=@STR+'*0'
SET @INDEX=CHARINDEX('*',@STR,@INDEX+1)
SET @C=@C+1
END
RETURN @STR
END
然后,试下效果SELECT DBO.FUN(COL)FROM
(SELECT '32'COL
UNION ALL SELECT '32*1'
UNION ALL SELECT '1')T