Generate a sequence number for corresponding numbers

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  April 5, 2018 at 10:32 am

  #403231

  I have a requirement , where i need to create a look up table to generate a Sequence number for each corresponding Loannumber.

  Example something like below. I am planning to create a table and insert original number and have a function to generate a Sequence number.

  Original Loannumber         Generated one
  0000012345            0000010001
  0000012346            0000010002
  0000012347            0000010003
  0000012348            0000010004
  0000012349            0000010005
  0000012350            0000010006
  0000012351            0000010007
  0000012352            0000010008

  CREATE TABLE LoanNumber
  (
  Loannumber varchar(10),
  GeneratedLoan Varchar(10)
  )

  Insert into LoanNumber values ('0000012345')        
  Insert into LoanNumber values ('0000012346')        
  Insert into LoanNumber values ('0000012347')        
  Insert into LoanNumber values ('0000012348')        
  Insert into LoanNumber values ('0000012349')
  Insert into LoanNumber values ('0000012350')        
  Insert into LoanNumber values ('0000012351')        
  Insert into LoanNumber values ('0000012352')

  NOTE : I cannot do identity column is datatype is  varchar also , need to add 0's in front

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  April 5, 2018 at 10:36 am

  #1985710

  komal145 - Thursday, April 5, 2018 10:32 AM

  I have a requirement , where i need to create a look up table to generate a Sequence number for each corresponding Loannumber.

  Example something like below. I am planning to create a table and insert original number and have a function to generate a Sequence number.

  Original Loannumber         Generated one
  0000012345            0000010001
  0000012346            0000010002
  0000012347            0000010003
  0000012348            0000010004
  0000012349            0000010005
  0000012350            0000010006
  0000012351            0000010007
  0000012352            0000010008

  CREATE TABLE LoanNumber
  (
  Loannumber varchar(10),
  GeneratedLoan Varchar(10)
  )

  Insert into LoanNumber values ('0000012345')        
  Insert into LoanNumber values ('0000012346')        
  Insert into LoanNumber values ('0000012347')        
  Insert into LoanNumber values ('0000012348')        
  Insert into LoanNumber values ('0000012349')
  Insert into LoanNumber values ('0000012350')        
  Insert into LoanNumber values ('0000012351')        
  Insert into LoanNumber values ('0000012352')

  NOTE : I cannot do identity column is datatype is  varchar also , need to add 0's in front

  Use an IDENTITY and set your VARCHAR() column as computed, to add in the leading zeros?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  April 5, 2018 at 12:18 pm

  #1985726

  Phil Parkin - Thursday, April 5, 2018 10:36 AM

  komal145 - Thursday, April 5, 2018 10:32 AM

  I have a requirement , where i need to create a look up table to generate a Sequence number for each corresponding Loannumber.

  Example something like below. I am planning to create a table and insert original number and have a function to generate a Sequence number.

  Original Loannumber         Generated one
  0000012345            0000010001
  0000012346            0000010002
  0000012347            0000010003
  0000012348            0000010004
  0000012349            0000010005
  0000012350            0000010006
  0000012351            0000010007
  0000012352            0000010008

  CREATE TABLE LoanNumber
  (
  Loannumber varchar(10),
  GeneratedLoan Varchar(10)
  )

  Insert into LoanNumber values ('0000012345')        
  Insert into LoanNumber values ('0000012346')        
  Insert into LoanNumber values ('0000012347')        
  Insert into LoanNumber values ('0000012348')        
  Insert into LoanNumber values ('0000012349')
  Insert into LoanNumber values ('0000012350')        
  Insert into LoanNumber values ('0000012351')        
  Insert into LoanNumber values ('0000012352')

  NOTE : I cannot do identity column is datatype is  varchar also , need to add 0's in front

  Use an IDENTITY and set your VARCHAR() column as computed, to add in the leading zeros?

  Something like this ?
  DROP TABLE LoanNumber
  GO
  CREATE TABLE LoanNumber
  (
  Loannumber varchar(10),
  ID INT Identity(1000,1),
  GeneratedLoan AS ('000000'+ ID ) 
  )

  Insert into LoanNumber (Loannumber) values ('0000012345')
  Insert into LoanNumber (Loannumber) values ('0000012346')
  Insert into LoanNumber (Loannumber) values ('0000012347')
  Insert into LoanNumber (Loannumber) values ('0000012348')
  Insert into LoanNumber (Loannumber) values ('0000012349')
  Insert into LoanNumber (Loannumber) values ('0000012350')
  Insert into LoanNumber (Loannumber) values ('0000012351')
  Insert into LoanNumber (Loannumber) values ('0000012352')

  But the Leading zeros are not adding up. Please correct me.

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  April 5, 2018 at 12:23 pm

  #1985727

  komal145 - Thursday, April 5, 2018 12:18 PM

  But the Leading zeros are not adding up. Please correct me.

  What should they add up to? I do not understand this comment.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  April 5, 2018 at 12:33 pm

  #1985728

  Phil Parkin - Thursday, April 5, 2018 12:23 PM

  komal145 - Thursday, April 5, 2018 12:18 PM

  But the Leading zeros are not adding up. Please correct me.

  What should they add up to? I do not understand this comment.

  It should be generated something like this '0000010001' instead they are populating like 10001

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  April 5, 2018 at 12:46 pm

  #1985731

  Try this

  DROP TABLE IF EXISTS #LoanNumber;

  CREATE TABLE #LoanNumber
(
  Loannumber  VARCHAR(10)
, ID    INT IDENTITY(1000, 1)
, GeneratedLoan AS RIGHT('0000000000' + CAST(ID AS VARCHAR(10)),10)
);

  INSERT #LoanNumber
(
  Loannumber
)
VALUES
(
  'xyz'
)
,(
  'abc'
);

  SELECT *
FROM #LoanNumber ln;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  April 5, 2018 at 1:11 pm

  #1985734

  Phil Parkin - Thursday, April 5, 2018 12:46 PM

  Try this

  DROP TABLE IF EXISTS #LoanNumber;

  CREATE TABLE #LoanNumber
(
  Loannumber  VARCHAR(10)
, ID    INT IDENTITY(10000, 1)
, GeneratedLoan AS RIGHT('0000000000' + CAST(ID AS VARCHAR(10)),10)
);

  INSERT #LoanNumber
(
  Loannumber
)
VALUES
(
  'xyz'
)
,(
  'abc'
);

  SELECT *
FROM #LoanNumber ln;

  Note the added 0 to conform to the original request that started at 10000.

  Also, to the original poster, you don't use the IDENTITY column for ANY purpose OTHER than the computed column's value.  You'll only select the GENERATED one.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  April 5, 2018 at 1:20 pm

  #1985735

  sgmunson - Thursday, April 5, 2018 1:11 PM

  Phil Parkin - Thursday, April 5, 2018 12:46 PM

  Try this

  DROP TABLE IF EXISTS #LoanNumber;

  CREATE TABLE #LoanNumber
(
  Loannumber  VARCHAR(10)
, ID    INT IDENTITY(10000, 1)
, GeneratedLoan AS RIGHT('0000000000' + CAST(ID AS VARCHAR(10)),10)
);

  INSERT #LoanNumber
(
  Loannumber
)
VALUES
(
  'xyz'
)
,(
  'abc'
);

  SELECT *
FROM #LoanNumber ln;

  Note the added 0 to conform to the original request that started at 10000.

  Also, to the original poster, you don't use the IDENTITY column for ANY purpose OTHER than the computed column's value.  You'll only select the GENERATED one.

  Thanks it worked. 😀

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  April 6, 2018 at 10:53 am

  #1985829

  komal145 - Thursday, April 5, 2018 10:32 AM

  I have a requirement , where i need to create a look up table to generate a Sequence number for each corresponding Loannumber.

  Example something like below. I am planning to create a table and insert original number and have a function to generate a Sequence number.

  Original Loannumber         Generated one
  0000012345            0000010001
  0000012346            0000010002
  0000012347            0000010003
  0000012348            0000010004
  0000012349            0000010005
  0000012350            0000010006
  0000012351            0000010007
  0000012352            0000010008

  CREATE TABLE LoanNumber
  (
  Loannumber varchar(10),
  GeneratedLoan Varchar(10)
  )

  Insert into LoanNumber values ('0000012345')        
  Insert into LoanNumber values ('0000012346')        
  Insert into LoanNumber values ('0000012347')        
  Insert into LoanNumber values ('0000012348')        
  Insert into LoanNumber values ('0000012349')
  Insert into LoanNumber values ('0000012350')        
  Insert into LoanNumber values ('0000012351')        
  Insert into LoanNumber values ('0000012352')

  NOTE : I cannot do identity column is datatype is  varchar also , need to add 0's in front

  Look up the CREATE SEQUENCE statement. 

  https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/

  Please post DDL and follow ANSI/ISO standards when asking for help.