Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pinkswansays
Starting Member
4 Posts |
 Posted - 2011-12-13 : 11:16:49
|
| I am TOTALLY new to SQL ... meaning most of the posts I have seen so far are over my head :/ So please be nice and explain as you would to a monkey ... I need to insert data into a column that has a pattern but is not a simple succession. The pattern is somewhat like this:01-01A01-01B01-02A01-02B 02-01A02-01B... I am using SQL Server, and I know the basics of insert into, delete, etc.Thank you! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-13 : 11:23:31
|
| can you explain the business rules using which you will generate the above pattern?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-12-13 : 12:09:33
|
| Found a good page showing how to build a custom id:http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-serverThe idea is to create a column which gets an auto number assigned by sql server (identity column)then create a calculated column which applies a function to the number to return a custom value.this calculated column is what you use for your "patterned number".Here is my attempt to modify the function used to generate your pattern. It is close but not there yet. I'm sure some-one else would know how to finish it.CREATE function CustomerNumber(@i int) returns char(6) as begin return ( char(@i / 50 % 10 + 48) + char(@i / 5 % 10 + 48)+ '-'+ char(@i / 25 % 10 + 48) + char(@i / 2.5 % 10 + 48)+ char(@i / 26 % 260 + 65) ) endselect x, dbo.CustomerNumber(x) from ( select 1 as x union all select 2 as x union all select 3 as x union all select 4 as x union all select 5 as x union all select 6 as x union all select 7 as x union all select 8 as x union all select 9 as x union all select 10 as x union all select 11 as x union all select 12 as x union all select 13 as x union all select 14 as x union all select 25 union all select 26 union all select 27 union all select 51 union all select 52 ) x |
|
|
|
pinkswansays
Starting Member
4 Posts |
Posted - 2011-12-13 : 12:10:07
|
quote:
Originally posted by visakh16
can you explain the business rules using which you will generate the above pattern?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I am not sure what you mean ... do you mean what is the pattern I am actually trying to put in? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-13 : 12:11:08
|
| yes..exactly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pinkswansays
Starting Member
4 Posts |
Posted - 2011-12-13 : 12:18:31
|
| @lappinThank you! That is what I was looking for. The sequence I posted is not actually what I am using in the db, so don't worry about that. I think I will be able to figure it out from the tut so thank you! |
|
|
|
pinkswansays
Starting Member
4 Posts |
Posted - 2011-12-13 : 12:23:06
|
| The pattern is:01-01V01-01A01-01D01-02V01-02A01-02D...01-17V01-17A01-17D (this pattern goes up to 17, then restarts as so:)02-01V02-01A02-01D02-02V02-02A02-02D...and so forth to 17 and then starts again with 03-01V |
|
|
|
|
|
|
inserting pattern? please be nice! :)