How to increment a column with varchar data type

Prev: SQLServer equivalent of Flashback
Next: Complex Statement - Newbie Needs Help

From: ganesh on 23 Feb 2010 07:03

---

please sens answer to me


From http://www.developmentnow.com/g/113_2006_4_0_0_737400/How-to-increment-a-column-with-varchar-data-type.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

From: jgurgul on 23 Feb 2010 09:06

---

Hi,

Appending extra chars to the front of keys hints towards poor design,
although not always the case. (I have however seen in the past very nasty
string manipulation in order to work round these sort of things)

Depending on your exact requirements I would try to stick with the built in
auto incrementing integer functionality e.g identity.

The alternative is to implement your own increments:


DROP TABLE [Seq]
DROP TABLE [Something]
CREATE TABLE [Seq](v INT)
CREATE TABLE [Something](SequenceID VARCHAR(10),Amount Money)
INSERT INTO [Seq] VALUES (0)
DECLARE @SequenceID INT;


INSERT INTO Something(SequenceID, Amount)
SELECT 'MH'+ CAST(SequenceID AS VARCHAR(10)), 150.00 FROM
(UPDATE dbo.Seq SET @SequenceID = v + 1, v += 1 OUTPUT Inserted.v) AS
T(SequenceID);

INSERT INTO Something(SequenceID, Amount)
SELECT 'MH'+ CAST(SequenceID AS VARCHAR(10)), 225.75 FROM
(UPDATE dbo.Seq SET @SequenceID = v + 1, v += 1 OUTPUT Inserted.v) AS
T(SequenceID);

SELECT * from [Seq]
SELECT * from [Something]

Jon

"ganesh" wrote:

> please sens answer to me
>
>
> From http://www.developmentnow.com/g/113_2006_4_0_0_737400/How-to-increment-a-column-with-varchar-data-type.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com/g/
> .
>

From: Gert-Jan Strik on 23 Feb 2010 15:19

---

ganesh wrote:
>
> please sens answer to me
>
> From http://www.developmentnow.com/g/113_2006_4_0_0_737400/How-to-increment-a-column-with-varchar-data-type.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com/g/


You have to provide a better specification. Let's say the current value
is "hello". In your opinion, what should the "increment" of this value
be?

Character type data types are not suitable to do calculations. Only
sorting and comparisons fit the natural behavior of such data types. If
you want to use increments then chose a discrete numeric data type such
int or bigint. Do not chose float, datetime, varchar, etc.

--
Gert-Jan

 | 
Pages: 1
Prev: SQLServer equivalent of Flashback
Next: Complex Statement - Newbie Needs Help