插入并递增1个字符 [英] insert and increment it by 1 character
本文介绍了插入并递增1个字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨我有两列。 sno作为第二列中的主键和字符。我想在第1列插入一个int。但是当插入第二个日期时,它应该自动插入B到第二行的字符列中,C插入第三个,D插入第四个等等。有什么办法吗?
解决方案
使用以下功能
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- =============================================
- 作者:Kuthuparakkal
- 创建日期:2013-08-08
- <跨越式s =code-comment>描述:地图编号到字母
- == ===========================================
CREATE FUNCTION dbo.fn_GetCharacter
(
@ SNo INT
)
退货 NVARCHAR (MAX)
AS
BEGIN
- 在此声明返回变量
DECLARE @Divident INT = @ SNo
DECLARE @ Character NVARCHAR 跨度>(MA X)= ' '
DECLARE @ Modulo INT
- 添加T-SQL语句以计算此处的返回值
WHILE ( @ Divident > 0 )
BEGIN
SET @ Modulo =( @Divident - 1 )% 26 ;
SET @ Character = CHAR ( 65 + @ Modulo )+ @ Character
SET @Divident =(( @Divident - @ Modulo )/ 26 );
END
- 返回函数的结果
RETURN @ Character
END
GO
现在创建你的表:
创建 < span class =code-keyword> TABLE dbo.tbl_myTable
(
[SNo] INT IDENTITY ( 1 , 1 )
,[Character] NVARCHAR (MAX)
)
现在插入像这样:
INSERT tbl_myTable DEFAULT VALUES
UPDATE tbl_myTable SET [CHARACTER] = dbo.fn_GetCharacter( SCOPE_IDENTITY ())
WHERE SNo = SCOPE_IDENTITY ()
hi i have two columns. sno as primary key and character in the second column. i'd like to insert a int the 1st column. but while inserting 2nd date it should insert automatically B in to that 2nd row of character column and C in 3rd and D in 4th and so on.. Is there any way?
解决方案
Use the following function
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kuthuparakkal -- Create date: 2013-08-08 -- Description: Map Number to Letter -- ============================================= CREATE FUNCTION dbo.fn_GetCharacter ( @SNo INT ) RETURNS NVARCHAR(MAX) AS BEGIN -- Declare the return variable here DECLARE @Divident INT = @SNo DECLARE @Character NVARCHAR(MAX)='' DECLARE @Modulo INT -- Add the T-SQL statements to compute the return value here WHILE(@Divident > 0) BEGIN SET @Modulo = (@Divident - 1) % 26; SET @Character = CHAR(65 + @Modulo) + @Character SET @Divident = ((@Divident - @Modulo) / 26); END -- Return the result of the function RETURN @Character END GO
Now Create your table:
CREATE TABLE dbo.tbl_myTable ( [SNo] INT IDENTITY(1,1) ,[Character] NVARCHAR(MAX) )
Now insert like this:
INSERT tbl_myTable DEFAULT VALUES UPDATE tbl_myTable SET [CHARACTER] = dbo.fn_GetCharacter(SCOPE_IDENTITY()) WHERE SNo = SCOPE_IDENTITY()
这篇关于插入并递增1个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文