sqlsever t-sql判断输入的数字重复出现的次数

作者:袖梨 2022-06-29


利用一个存储过程

 

 代码如下 复制代码
ALTER PROC NumShowTimes
(
@numValue NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @TempTable TABLE(num NVARCHAR(4))---定义存放数字的表
    DECLARE @TempNum NVARCHAR(4) --
    DECLARE @Value nvarchar(max)
    SET @Value = @numValue
    ---判断输入的是否是数字
    WHILE(LEN(@numValue)>0)
        BEGIN
            SET @TempNum=SUBSTRING(@numValue,LEN(@numValue),1)
            SET @numValue=SUBSTRING(@numValue,1,(LEN(@numValue)-1))
            IF @TempNum LIKE'[^0-9]'
                BEGIN
                    PRINT 'YOU INPUT NOT NUM'
                    RETURN
                END
        END
    ----获取数据插入表中
    WHILE(LEN(@Value)>0)
        BEGIN
            SET @TempNum=SUBSTRING(@Value,LEN(@Value),1)
            SET @Value=SUBSTRING(@Value,1,(LEN(@Value)-1))
            INSERT INTO @TempTable VALUES (@TempNum)
        END
    SELECT num,count(num)AS [count] FROM @TempTable GROUP BY num
END

 

去掉判断输入的是否是数字那个while循环,就显示所输入的所字符出现的次数,包括汉字

SQL利用Case When Then Else End 多条件判断 以指定的次数重复字符 (生成当天最大顺序编号)

 

 

 代码如下 复制代码

Select

Case

    When a is not null then a

    When b is not null then b

    When c is not null then c

    When d is not null then d

    Else ''

End 列名

From Table Name


SQL利用Case When Then多条件判断

 代码如下 复制代码

Select top 100 State,JoinState,
(case

when State=1 and Joinstate=0 then 2

when State=1 and JoinState=1 then 1

else 0

end )

as usestate

from UserInfo

(生成当天最大顺序编号)

select substring(convert(varchar(8), getdate(), 112), 1, 8)

       +'568'

       + replicate('0', 4-len(max_ContractNo)) --以指定的次数重复字符表达式

       + cast(max_ContractNo as varchar(4))

from (select

case

       when substring(max(My_ContractNo), 12, 4) is null then '1'

       else cast(cast(substring(max(My_ContractNo), 12, 4) as bigint) + 1 as varchar(8))

    end max_ContractNo

       from UserContractNo

WHERE datediff(Day,Adddate,getdate())=0

       ) s


CREATE TABLE [UserContractNo](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [ApplyUserDetailID] [int] NOT NULL,

    [My_ContractNo] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT     [DF_UserContractNo_My_ContractNo] DEFAULT (''),

    [AddDate] [datetime] NOT NULL CONSTRAINT [DF_UserContractNo_AddDate] DEFAULT (getdate()),

    [Remarks] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_UserContractNo_Remarks] DEFAULT (''),

CONSTRAINT [PK_UserContractNo] PRIMARY KEY CLUSTERED

([Id] ASC) ON [PRIMARY]

) ON [PRIMARY]

相关文章

精彩推荐