Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Generating Alphanumeric Serial numbers

May 24 2013 12:00AM by Madhivanan   

One of the common requirements is to generate an alphanumeric incremental values like PA0000000001, PA0000000002, etc. There can be several methods to to this. I will list out some of them

Method 1: Create an identity column and concatenate in SELECT statement when presenting data

Declare  @test table (cust_id int identity(1,1), cust_name varchar(50))
insert into @test(cust_name)
select 'Murugan' union all
select 'Suresh' union all
select 'Naresh' union all
select 'Kumar' union all
select 'Ezhilan'
select 'PA'+replace(str(cust_id,10),' ','0') as cust_id,cust_name from @test

The result is

cust_id      cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh 
PA0000000004 Kumar 
PA0000000005 Ezhilan 

Method 2 : Create an identity column and use computed column
Declare  @test table (cust_id int identity(1,1), cust_name varchar(50), cust_id_new as 'PA'+replace(str(cust_id,10),' ','0'))
insert into @test(cust_name)
select 'Murugan' union all
select 'Suresh' union all
select 'Naresh' union all
select 'Kumar' union all
select 'Ezhilan'
select cust_id_new,cust_name from @test

The result is
cust_id_new  cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh 
PA0000000004 Kumar 
PA0000000005 Ezhilan 

Method 3 : Create an alphanumeric incremental value before adding to a table
Declare  @test table (cust_id varchar(12), cust_name varchar(50))
declare @cust_id varchar(12)
set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Murugan'

set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Suresh'

set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Naresh'

select cust_id, cust_name from @test

The result is
cust_id      cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh 
I prefer method 1 or 2 for the simple approch

Tags: 


Madhivanan
3 · 40% · 12936
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Hi Madhivanan, As usual a very informative article, Would like to add one more method that is compatible with SQL Server 2012 and above using FORMAT function.

    Declare  @test table (cust_id int identity(1,1), cust_name varchar(50))
    insert into @test(cust_name)
    select 'Murugan' union all
    select 'Suresh' union all
    select 'Naresh' union all
    select 'Kumar' union all
    select 'Ezhilan'
    
    select 'PA'+FORMAT(cust_id, '000000000#')  as cust_id,cust_name from @test
    

    Reference : http://raresql.com/2012/10/09/sql-server-200520082012-leading-zero-to-number/

    commented on May 25 2013 3:46PM
    mimran18
    1606 · 0% · 12
  • Hi mimran18

    Thanks for the feedback. Yes from version 2012 onwards you can also use FORMAT function

    commented on May 30 2013 10:16AM
    Madhivanan
    3 · 40% · 12936
  • nice xplanation

    commented on Jan 27 2014 7:02AM
    aasif2707
    2380 · 0% · 5

Your Comment


Sign Up or Login to post a comment.

"Generating Alphanumeric Serial numbers" rated 5 out of 5 by 1 readers
Generating Alphanumeric Serial numbers , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]