auto generate customer id script

SELECT
-- evaluates the 9 first characters of the customer name and removes any blanks
-- other characters can be removed) in between those first 9 characters for a
-- total of 8, adds an extra zero if needed to complete 9 characters
CASE LEN(UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')))
WHEN 8 THEN UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) + '0'
ELSE UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) END +

-- accounts for the rest of the string, uses the rank function to do the numbering,
-- partitioning by customer name. Just in case there is more than one customer
-- with the same starting 9 characters, rank() will number them sequentially
SUBSTRING('000', 1, 3 - LEN(CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE
(SUBSTRING(CUSTNAME, 1, 9), ' ', '') ORDER BY CUSTNAME))))
+ CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE(SUBSTRING(CUSTNAME, 1,
9), ' ', '') ORDER BY CUSTNAME))
FROM RM00101
ORDER BY CUSTNAME

source- http://dynamicsgpblogster.blogspot.com/2010/02/sql-autogenarating-customer-ids.html