April 5, 2018 at 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
April 5, 2018 at 10:36 am
komal145 - Thursday, April 5, 2018 10:32 AMI 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 0000010008CREATE 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 5, 2018 at 12:18 pm
Phil Parkin - Thursday, April 5, 2018 10:36 AMkomal145 - Thursday, April 5, 2018 10:32 AMI 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 0000010008CREATE 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.
April 5, 2018 at 12:23 pm
komal145 - Thursday, April 5, 2018 12:18 PMBut the Leading zeros are not adding up. Please correct me.
What should they add up to? I do not understand this comment.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 5, 2018 at 12:33 pm
Phil Parkin - Thursday, April 5, 2018 12:23 PMkomal145 - Thursday, April 5, 2018 12:18 PMBut 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
April 5, 2018 at 12:46 pm
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 5, 2018 at 1:11 pm
Phil Parkin - Thursday, April 5, 2018 12:46 PMTry 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
April 5, 2018 at 1:20 pm
sgmunson - Thursday, April 5, 2018 1:11 PMPhil Parkin - Thursday, April 5, 2018 12:46 PMTry 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. 😀
April 6, 2018 at 10:53 am
komal145 - Thursday, April 5, 2018 10:32 AMI 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 0000010008CREATE 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply