October 1, 2012 at 1:03 pm
Hi Forum,
I need to generate a sequence number using SSIS , this value is stored in the column as a varchar(15),
001-00-000001
001-00-000002
001-00-000003
It will NEVER reach the value 001-00-999999
I'm trying to use a cte to generate a number
WITH cteSequence ( SeqNo) as
(
SELECT 00100000001
UNION ALL
SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < 10000000000
)
Is there any other way we cn acheive this task? with leading 001-00-
thanks in advance !!
October 1, 2012 at 1:35 pm
jampabsatish (10/1/2012)
Hi Forum,I need to generate a sequence number using SSIS , this value is stored in the column as a varchar(15),
001-00-000001
001-00-000002
001-00-000003
It will NEVER reach the value 001-00-999999
I'm trying to use a cte to generate a number
WITH cteSequence ( SeqNo) as
(
SELECT 00100000001
UNION ALL
SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < 10000000000
)
Is there any other way we cn acheive this task? with leading 001-00-
thanks in advance !!
So you want a number from 1 to 999,999. Look at: http://www.compshack.com/sql/sql-generate-sequence-number for generating the sequential number.
The rest is just string manipulation. You want to left pad the sequential number with zeros so that it is six digits long. Something like: SELECT RIGHT('00000' + CAST(SequentialNumber AS varchar(6), 6)
Then concatenate the '001-00-' on the front of that.
I hope that makes sense,
Rob
October 1, 2012 at 1:49 pm
concatente 001-00- to 000001 (first number in the list)
in my case 1 is different from 000001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply