How to increment a column with varchar data type

hi,
is there any method for incrementing the varchar field
i have an emp table with columns
create table emprecord(eno varchar(10), ename varchar(20))
i cant give eno column with identity field because identity column must
be of data type int, bigint, smallint, tinyint, or decimal or numeric
insert into emprecord values ('MH1001','satish')
insert into emprecord values ('MH1002','rehman')
my problem is there any method --when i pass only the ename the eno
field should be automatically incremented as MH1003 --each time it has
to check for the highest eno ie.., MH1002 is my highest eno in the
table emprecord
i think there is sequence option in MS Sql server-- if so how to use
sequence
pls help me
thanks
satishTry a search about "SQL Server identity values", but it supports integer
values only. You might need to make appropriate modifications on your table.
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com ?...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||You could use a computed column instead|||satish
create table #t
(
rowid int not null identity(1,1) primary key,
empno AS 'MH'+CAST(rowid AS VARCHAR(10)),
empname VARCHAR (50)
)
insert into #t(empname) VALUES ('Smith')
insert into #t (empname)VALUES ('Clinton')
insert into #t (empname) VALUES ('Brown')
select empno,empname from #t
drop table #t
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||there are no identity columns defined in my table in that case how to
do
this is my table
create table emprecord(eno varchar(10), ename varchar(20))
i always pass only ename|||It's better to break empno into empPrefix (varchar(2)) and empSerial (int).
or, if the table structure cannot be modified, I will opt to use stored
procedure (and/or other programming means).
create table #MyTempEmpRecord06041402
(
empno varchar(10),
empname VARCHAR (50)
)
go
-- Assumes that @.myEmpnoPrefix is always having 2 characters
create procedure #MyInsertEmpname06041402
@.myEmpnoPrefix varchar(2),
@.myEmpname varchar(50)
as
insert #MyTempEmpRecord06041402
select @.myEmpnoPrefix
+ (select convert(varchar(8), convert(int, isnull(max(substring(empno,
1 + len(@.myEmpnoPrefix), 8)), '0')) + 1) as MySerial
from #MyTempEmpRecord06041402
where left(empno, len(@.myEmpnoPrefix)) = @.myEmpnoPrefix)
as empno,
@.myEmpname as empname
go
exec #MyInsertEmpname06041402 'MH', 'Smith'
exec #MyInsertEmpname06041402 'MH', 'Clinton'
exec #MyInsertEmpname06041402 'MP', 'Smith'
exec #MyInsertEmpname06041402 'MP', 'Brown'
exec #MyInsertEmpname06041402 'MH', 'Brown'
go
select * from #MyTempEmpRecord06041402
go
drop procedure #MyInsertEmpname06041402
go
drop table #MyTempEmpRecord06041402
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145007066.106515.192790@.t31g2000cwb.googlegroups.com ?...
> there are no identity columns defined in my table in that case how to
> do
> this is my table
> create table emprecord(eno varchar(10), ename varchar(20))
> i always pass only ename
>