Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-19
16
Medium Priority
?
187 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 143

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 143

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

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 143

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 143

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 70

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 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