T-SQL Script to create a reference number from a client name
Posted on 2007-11-19
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!)