declare @str varchar(6) set @str='32*1' select @str+RIGHT('*0*0*0',6-LEN(@str)) 不知道我有没有理解正确?
既然这样,考虑用自定函数不~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
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更正一下~~
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
USE tempdb GODECLARE @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 */
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
set @str='32*1'
select @str+RIGHT('*0*0*0',6-LEN(@str))
不知道我有没有理解正确?
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
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更正一下~~
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
GODECLARE @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
*/
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