insert and increment it by 1 character

Question

0.00/5 (No votes)

See more:

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?

Posted 7-Aug-13 19:36pm

ARUN K P

Add a Solution

Comments

Kuthuparakkal 8-Aug-13 1:40am

Post sample output

ARUN K P 8-Aug-13 1:57am

how to attach a file in this? i've taken a screen shot but there is no option of attaching a file here.

ARUN K P 8-Aug-13 1:58am

sno character
1 a
2 b
3 c

tats how my output should be

Kuthuparakkal 8-Aug-13 3:08am

See the solution posted....

Status BreakPoint 8-Aug-13 1:40am

Which database do you use?

ARUN K P 8-Aug-13 1:51am

i m using sql server 2008

ArunRajendra 8-Aug-13 1:58am

what happens when you reach Z?

ARUN K P 8-Aug-13 1:59am

i ll set the no of rows count to particular no . once if reaches z then it should start with aa, ab, ac...ba,bb,bc.. like tat.

[no name] 8-Aug-13 2:32am

Still I don't get the purpose of your requirement. Why you need the columns like an excel sheet columns. Is this some thing related to the excel sheet or merely you have asked this question. It would be better if you mention your requirement with a clean problem statement so that we can suggest you the best way to do things.

ARUN K P 8-Aug-13 2:56am

i'm new into sql. i wanna learn these basics to get going. i've gone through the basics in w3 schools.com. now i'm practing writing queries, stored procedures, joins etc. so i wanna know how to implement these kind of things if i get requirement like this in future.

CodeBlack 8-Aug-13 2:55am

how do you want to insert ? using SQL or programatically ?

ARUN K P 8-Aug-13 3:02am

using sql only

Maciej Los 8-Aug-13 3:45am

What happen if number of rows reaches 999 999 999 999 value?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Accepted Answer · 2013-08-07T21:08:00

Use the following function

SQL

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:

SQL

 CREATE TABLE dbo.tbl_myTable
(
 [SNo] INT IDENTITY(1,1)
,[Character] NVARCHAR(MAX)
)

Now insert like this:

SQL

INSERT tbl_myTable DEFAULT VALUES
UPDATE tbl_myTable SET [CHARACTER] = dbo.fn_GetCharacter(SCOPE_IDENTITY())
WHERE SNo = SCOPE_IDENTITY()