Append Number to make a unique code

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Append Number to make a unique code

Author

Topic

Tommm83
Starting Member

4 Posts

Posted - 2011-05-10 : 06:39:00

Hi,

I am having real trouble creating a script to create a unique code for a product.

I have created a four letter prefix using a substring and added this to my table, but I want to append a four digit number after this to make the code unique.

My data now looks like the following:

CODE DESC
APPL Apple Green
APPL Apple Red
ORAN Orange Big
ORAN Orange Small

However I want to make it look like this:

CODE DESC
APPL0001 Apple Green
APPL0002 Apple Red
ORAN0001 Orange Big
ORAN0002 Orange Small

I obviously need to iterate through somehow to do this depending on the number of duplicate codes, but I can't work out how to do it!

Can anyone help?

Thanks,
Tom

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 07:16:20

This looks like homework. What have you tried so far?

Jim

Everyday I learn something that somebody else already knew

Tommm83
Starting Member

4 Posts

Posted - 2011-05-10 : 07:37:21

Definately not homework! I've just simplified it all down to make the question a little clearer.

The bones of what I have done is below. I just can't work out the iteration part to make it append the numbers.

The actual data looks a bit like this:

#MATERIALPREFIX:

Prefix Desc
7ORM 7 or more
ADDI Additional WC
ADEQ Adequate
ALLT All types
ALLT All types (except decs)
ALLT All types (HH)

#COUNTOFDUPLICATES:

Prefix TotalCount
ALLT 3
ALRE 4
BLOC 2
BRIC 6

I also have the issue of only being able to create my own temporary tables as I am not allowed write access to the main tables.

Hope this makes sense,

Tom

------------------------------

select * into #countofduplicates from
(
SELECT Prefix, COUNT(*) as TotalCount
FROM #materialprefix
GROUP BY Prefix
HAVING COUNT(*) > 1
) as t

declare @i int
declare @rows int

set @i = 1
set @rows = @@rowcount

while @i < @rows
begin

update #countofduplicates
set totalcount = totalcount *2
where @@rowcount = @i

set @i = @i + 1
end

select * from #countofduplicates

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 07:39:29

For your original 'bare bones' version, try this. we'd need better sample data and expected output to make any further changes.

DECLARE @Table TABLE(Code varchar(6),[Desc] varchar(50))

INSERT INTO @Table
SELECT 'APPL','Apple Green' UNION ALL
SELECT 'APPL','Apple Red' UNION ALL
SELECT 'ORAN','Orange Big' UNION ALL
SELECT 'ORAN','Orange Small'

SELECT CODE + RIGHT('0000'+convert(varchar(10),rank() over(partition by code order by [Desc])
)
,4)

FROM @Table

Everyday I learn something that somebody else already knew

Tommm83
Starting Member

4 Posts

Posted - 2011-05-10 : 07:48:01

Based on the data sets I provided above (I hope those are better), I have done the following:

SELECT Prefix +
RIGHT
(
'0000'+convert(varchar(10),rank() over(partition by Prefix order by Prefix)),4
)
FROM #materialprefix

This has appended '0001' to the end of everything. It's definately heading in the right direction, its just making the unique entries now.

The resulting dataset looks like this:

7ORM0001
ADDI0001
ADEQ0001
ALLT0001
ALLT0001
ALLT0001
ALRE0001

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 07:57:57

You partitioned by the prefix and ordered by the prefix, so everything is ranked #1. Try partitioning by the prefix and ordering by the desc.

Jim

Everyday I learn something that somebody else already knew

Tommm83
Starting Member

4 Posts

Posted - 2011-05-10 : 08:03:39

My mistake!

That's perfect! Never used rank() and partitioning before! Fantastic stuff.

Thanks a lot,
Tom

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 08:10:49

The over() clause and the windowed functions are pretty cool. Also look at dense_rank() and row_number().

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources