Solved

T-SQL Script to create a reference number from a client name

Posted on 2007-11-19
16
174 Views
Last Modified: 2010-03-20
Hi, I'm tying myself in knots with this one so wondered if anyone could point me in the right direction.

I have a table containing the names of around 3,000 companies (TBL_Companies)

I need to create a reference number for each company based on the name. In simple terms it will be the first four letters of their name followed by a 3 digit number, eg:

Able & Co - ABLE001
South London Cleaners - SOUT001
South Yorkshire Council - SOUT002

So it will give the next number for each company in the group.
 
I haven't a clue how to do this so all suggestions gratefully received! (I have considered resorting to Excel which is how desperate I am!)
0
Comment
Question by:pauldonson
  • 8
  • 7
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312396
please check out this script:
declare @tbl_companies table ( name varchar(50), code varchar(10))
insert into @tbl_companies (name) values  ('Able & Co - ABLE001' )
insert into @tbl_companies (name) values  ('South London Cleaners - SOUT001' )
insert into @tbl_companies (name) values  ('South Yorkshire Council - SOUT002' ) 
select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from @tbl_companies i where left(i.name,4) = left(t.name,4) ) as varchar(10)), 3) code
from @tbl_companies  t
order by t.name

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312402
sorry, 1 condition missing:
declare @tbl_companies table ( name varchar(50), code varchar(10))
insert into @tbl_companies (name) values  ('Able & Co - ABLE001' )
insert into @tbl_companies (name) values  ('South London Cleaners - SOUT001' )
insert into @tbl_companies (name) values  ('South Yorkshire Council - SOUT002' ) 
select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from @tbl_companies i where left(i.name,4) = left(t.name,4) and i.name <= t.name ) as varchar(10)), 3) code
from @tbl_companies  t
order by t.name

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20312591
One tiny problem, the table has duplicates in it (which is OK) - so the script above will number them all 004 if there are 4 with the same name instead of 001,002,003,004

Sorry!
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312640
is there any other field that makes the row "unique"?

if yes, we can adjust the subquery to take that field into account
0
 

Author Comment

by:pauldonson
ID: 20312648
Company_ID is unique
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312713
ok, let's see what this gives:


select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.name,4) = left(t.name,4) and ( i.name < t.name or  ( i.name = t.name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t
order by t.name

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313106
Yep, that's done it! Thanks.

One last thing just so I don't do any damage - how should I format it to do an update, here is what I have:

Update TBL_Companies
Set Sage_account = select t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t
order by t.company_name


Should this be OK?
0
 

Author Comment

by:pauldonson
ID: 20313117
Hum, apparantly not! I just tested it on the backup database.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313151
what about this:


Update t
Set Sage_account = select t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313249
I get this error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313441
the keyword SELECT was too much


Update t
Set Sage_account = t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) 
from tbl_companies  t

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313464
Sorry about this, I get this message now:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20314605
looks like I had a leftover from the query...


Update t
Set Sage_account = upper(left(t.company_name, 4)) 
 + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) 
from tbl_companies  t

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20316297
Since company names can change, you really shouldn't be using the name in the ref num.  Just use a dummy, sequential number you arbitrarily assign; in SQL Server, an IDENTITY column is a typical way to do that.

Seriously, honestly, you *don't* want to include part of the name in your identifier.
0
 

Author Comment

by:pauldonson
ID: 20319189
Hi Scott, thanks for the tip - however this is the Account Code that will be used in their Sage Accounting system - People always like to have these as part of the name (which I know is a pain but that's accountants for you!!!)
0
 

Author Comment

by:pauldonson
ID: 20319964
angell, that did the trick thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 38
Need some help to cast ntext to nvarchar SQL 2000 7 32
Sql Query 6 65
SQL Query assistance 16 23
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question