字符串值自动递增 [英] Auto Increment in string value

问题描述

大家好，



我需要查询字符串值中的自动增量 -



我需要XS000001，XS000009 .... XS000099 ... XS000999，XS009999，XS099999，XS999999



请帮帮我...

Hi All,

I need query for auto increment in string value-

I need XS000001,XS000009....XS000099...XS000999,XS009999,XS099999,XS999999

Please help me...

推荐答案

我不知道自动递增所需的值的来源，但您应该能够使用如下所示的T-SQL代码来解决问题：

I don't know the source for the values you need to auto-increment, but you should be able to solve the problem using T-SQL code that looks something like this:

DECLARE @StartAt INT = 0
SELECT 'XS' + REPLACE(STR(1 + @StartAt, 6), SPACE(1), '0')
-- returns XS000001

为例子，假设你的起始值是745;然后代码如下所示：

For the sake of example, suppose your starting value is 745; then the code looks like this:

DECLARE @StartAt INT = 745
SELECT 'XS' + REPLACE(STR(1 + @StartAt, 6), SPACE(1), '0')
-- returns XS000746

如果要增加整数值数据库表，然后解决方案可能如下所示：

If you are incrementing an integer value in a database table, then the solution might look like this:

CREATE TABLE #TempCounter (ID INT)
INSERT INTO #TempCounter (ID) VALUES (1)
INSERT INTO #TempCounter (ID) VALUES (2)
INSERT INTO #TempCounter (ID) VALUES (3)
INSERT INTO #TempCounter (ID) VALUES (4)
DECLARE @StartAt INT = (SELECT MAX(ID) FROM #TempCounter)
SELECT 'XS' + REPLACE(STR(1 + @StartAt, 6), SPACE(1), '0')
DROP TABLE #TempCounter
-- returns XS000005

如果要在数据库表中递增字符串值，那么解决方案可能如下所示：

If you are incrementing a string value in a database table, then the solution might look like this:

CREATE TABLE #TempCounter (ID NVARCHAR(8))
INSERT INTO #TempCounter (ID) VALUES ('XS000100')
INSERT INTO #TempCounter (ID) VALUES ('XS000101')
INSERT INTO #TempCounter (ID) VALUES ('XS000102')
INSERT INTO #TempCounter (ID) VALUES ('XS000103')
DECLARE @StartAt INT = (SELECT CAST(RIGHT(MAX(ID),6) AS INT) FROM #TempCounter)
SELECT 'XS' + REPLACE(STR(1 + @StartAt, 6), SPACE(1), '0')
DROP TABLE #TempCounter
-- returns XS000104

如果这回答了您的问题，请告诉我。我的例子假设是SQL Server，但是你可能正在使用不同的平台？

Let me know if this answers your question. My examples assume SQL Server, but perhaps you are using a different platform?

这篇关于字符串值自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！