Append a 3 digit number

Question

Post reply

Append a 3 digit number

Mike Chiodo-346248

SSC Enthusiast

Points: 183
More actions
August 21, 2006 at 8:37 am

#114615

Hello,
First let me qualify this-I'm fairly new at SQL and programming in general. My training has been trial by fire so far 🙂
I have a SQL table with a field called pos_no. The field pos_no currently has a 6 digit number in it, such as 100001. Out of 200 records in the table there may be 20 with the same value in the pos_no field. For example, it may look like this:
100001
100001
540053
550008
550008
550008
883500
Some are duplicates but some are unique.
I'd like to write a script that appends a -XXX to the end of each one, and counts up one for each previous value that is the same. For example, I'd like it to look like this:
100001-001
100001-002
540053-001
550008-001
550008-002
550008-003
883500-001
Any help or pointers in the right direction would be greatly appreciated.
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SALIM ALI SSC Eights! Points: 859 More actions · Answer 1

Hi,

I'm sure there's a better way of doing this using joins but this is all I can come up with for now:

set nocount on

create table #test1 (accno varchar(50))

create table #test2 (seq int identity(1,1), accno2 varchar(50))

insert #test1 values (100001)

insert #test1 values (100048)

insert #test1 values (100001)

insert #test1 values (100002)

insert #test1 values (100001)

insert #test1 values (200567)

insert #test1 values (345000)

insert #test1 values (456730)

insert #test1 values (234555)

insert #test1 values (456730)

insert #test1 values (112233)

insert #test1 values (443322)

insert #test1 values (334455)

insert #test1 values (112233)

insert #test1 values (443322)

insert #test1 values (112233)

insert #test1 values (334455)

insert into #test2 (accno2)

select accno from #test1 order by accno

declare @increment int, @current varchar(50), @previous varchar(50), @min-2 int, @max-2 int

select @increment=0,

@previous='000000',

@min-2=(select min(seq) from #test2),

@max-2=(select max(seq) from #test2)

while @max-2>=@min

BEGIN

select @current=(select accno2 from #test2 where seq=@min)

if @current = @previous

select @increment=@increment+1

else

select @increment=1

print @current + '-' + right('000' + cast(@increment as varchar) , 3)

select @previous=@current, @min-2=@min+1

END

drop table #test1

drop table #test2

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

You should be able to replace the print command with an update etc if necessary.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

You may try this

DECLARE @tbl TABLE

(ActualValue VARCHAR(30),

UpdatedValue VARCHAR(20))

INSERT INTO @tbl(ActualValue) vALUES (100001)

INSERT INTO @tbl(ActualValue) vALUES (100048)

INSERT INTO @tbl(ActualValue) vALUES (100001)

INSERT INTO @tbl(ActualValue) vALUES (100002)

INSERT INTO @tbl(ActualValue) vALUES (100001)

INSERT INTO @tbl(ActualValue) vALUES (200567)

INSERT INTO @tbl(ActualValue) vALUES (345000)

INSERT INTO @tbl(ActualValue) vALUES (456730)

INSERT INTO @tbl(ActualValue) vALUES (234555)

INSERT INTO @tbl(ActualValue) vALUES (456730)

INSERT INTO @tbl(ActualValue) vALUES (112233)

INSERT INTO @tbl(ActualValue) vALUES (443322)

INSERT INTO @tbl(ActualValue) vALUES (334455)

INSERT INTO @tbl(ActualValue) vALUES (112233)

INSERT INTO @tbl(ActualValue) vALUES (443322)

INSERT INTO @tbl(ActualValue) vALUES (112233)

INSERT INTO @tbl(ActualValue) vALUES (334455)

DECLARE @RowId VARCHAR(20)

DECLARE @UpdatedValue INT

UPDATE Source

SET @UpdatedValue = (CASE

WHEN @RowId = ISNULL(Source.ActualValue,0) THEN ISNULL(@UpdatedValue,0)+1

ELSE ISNULL(Source.UpdatedValue,0)

END),

Source.UpdatedValue = ActualValue+'-'+REPLICATE('0',3-LEN(LTRIM(RTRIM(@UpdatedValue))))+CAST(@UpdatedValue AS VARCHAR(10)),

@RowId = ISNULL(Source.ActualValue,0)

FROM @tbl AS Source

SELECT * FROM @tbl

Ram

Bhushan Default port Points: 1427 More actions · Answer 3

declare @t table (srno int)

insert into @t values (100001)

insert into @t values (540053)

insert into @t values (550008)

insert into @t values (883500)

update

a

set

a.Srno = cast(a.Srno as varchar) + (REPLICATE('0', 2))+ cast(b.Seq# as varchar)

from

(select row_number() over(partition by srno order by srno) Seq#, Srno from @t)a

join

(select row_number() over(partition by srno order by srno) Seq#, Srno from @t) b

on

a.Srno = b.Srno

and a.Seq# = b.Seq#

select * from @t

Ram Ram Ten Centuries Points: 1079 More actions · Answer 4

My Script requires ordering of records before update and hence

DECLARE @tbl1 TABLE

(ActualValue VARCHAR(30),

UpdatedValue VARCHAR(20))

DECLARE @tbl TABLE

(ActualValue VARCHAR(30),

UpdatedValue VARCHAR(20))

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (100048)

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (100002)

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (200567)

INSERT INTO @tbl1(ActualValue) vALUES (345000)

INSERT INTO @tbl1(ActualValue) vALUES (456730)

INSERT INTO @tbl1(ActualValue) vALUES (234555)

INSERT INTO @tbl1(ActualValue) vALUES (456730)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (443322)

INSERT INTO @tbl1(ActualValue) vALUES (334455)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (443322)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (334455)

INSERT INTO @tbl (ActualValue) SELECT ActualValue FROM @tbl1 ORDER BY ActualValue

DECLARE @RowId VARCHAR(20)

DECLARE @UpdatedValue INT

UPDATE Source

SET @UpdatedValue = (CASE

WHEN @RowId = ISNULL(Source.ActualValue,0) THEN ISNULL(@UpdatedValue,0)+1

ELSE ISNULL(Source.UpdatedValue,0)

END),

Source.UpdatedValue = ActualValue+'-'+REPLICATE('0',3-LEN(LTRIM(RTRIM(@UpdatedValue))))+CAST(@UpdatedValue AS VARCHAR(10)),

@RowId = ISNULL(Source.ActualValue,0)

FROM @tbl AS Source

SELECT * FROM @tbl

Ram

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

Nicely done, Ram. The only problem is that the first sequence number comes out as -000 instead of -001 as the original requestor posted. However, that's an easy fix because of the good way you wrote the code. Here's the fix and a couple of shortcuts...

DECLARE @RowId VARCHAR(20)

DECLARE @UpdatedValue INT

SET @RowID = -1

SET @UpdatedValue=-1

UPDATE Source

SET @UpdatedValue = CASE

WHEN @RowId = Source.ActualValue

THEN @UpdatedValue+1

ELSE 1

END,

Source.UpdatedValue = ActualValue+'-'+REPLACE(STR(@UpdatedValue,3),' ','0'),

@RowId = Source.ActualValue

FROM @tbl AS Source

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 6

And one more way to do that - taking advantage of IDENTITY column (I'm using part of Ram's code, so that you can compare the differences):

/*prepare tables*/

DECLARE @tbl1 TABLE (ActualValue VARCHAR(30))

DECLARE @tbl TABLE

(rownr int IDENTITY,

ActualValue VARCHAR(30),

UpdatedValue VARCHAR(20))

/*insert test data*/

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (100048)

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (100002)

INSERT INTO @tbl1(ActualValue) vALUES (100001)

INSERT INTO @tbl1(ActualValue) vALUES (200567)

INSERT INTO @tbl1(ActualValue) vALUES (345000)

INSERT INTO @tbl1(ActualValue) vALUES (456730)

INSERT INTO @tbl1(ActualValue) vALUES (234555)

INSERT INTO @tbl1(ActualValue) vALUES (456730)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (443322)

INSERT INTO @tbl1(ActualValue) vALUES (334455)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (443322)

INSERT INTO @tbl1(ActualValue) vALUES (112233)

INSERT INTO @tbl1(ActualValue) vALUES (334455)

/*insert ordered data into intermediary table*/

INSERT INTO @tbl (ActualValue) SELECT ActualValue FROM @tbl1 ORDER BY ActualValue

/*calculate results in intermediary table - this is the actual work*/

UPDATE Source

SET Source.UpdatedValue = Source.ActualValue

+ '-'

+ RIGHT('000'+ CAST(Source.rownr-firstrow.min_rownr+1 AS VARCHAR(10)),3)

FROM @tbl AS Source

/*derived table "firstrow" finds lowest row number for each ActualValue*/

JOIN (SELECT t.ActualValue, MIN(t.rownr) as min_rownr

FROM @tbl t GROUP BY t.ActualValue) AS firstrow

ON firstrow.ActualValue = source.ActualValue

/*display results to check*/

SELECT * FROM @tbl

After you have checked, that the results are what you need, you can update your base table. Since you didn't specify anything about your table, I won't go into that part. I hope the idea is clear... you number the rows using identity column, and since they were ordered, you can easily calculate what to append to value in each row by taking actual row number and subtracting smallest row number for the same ActualValue ( + 1 if you don't want to start at 0).

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Another way of doing it, without loops or identity.

It does however need a numbers table.

This is basically a technique that Itzik wrote about in SQL Server Mag June 2005 - 'Assigning Row Numbers for Non-Unique Rows'. That article explains in detail how it works.

Borrowing the #temp1 table from previous post in the thread:

create table #test1 (accno varchar(50))

go

insert #test1 values (100001)

insert #test1 values (100048)

insert #test1 values (100001)

insert #test1 values (100002)

insert #test1 values (100001)

insert #test1 values (200567)

insert #test1 values (345000)

insert #test1 values (456730)

insert #test1 values (234555)

insert #test1 values (456730)

insert #test1 values (112233)

insert #test1 values (443322)

insert #test1 values (334455)

insert #test1 values (112233)

insert #test1 values (443322)

insert #test1 values (112233)

insert #test1 values (334455)

go

This is the query, 1st column is just the original accno, the 2nd is the enumerated and concatenated endresult.

select z.accno, z.accno + right('00' + cast(n.n as varchar(3)), 3)

from ( select y.accno,

count(*) as dupes,

(select count(*) from #test1 x where x.accno < y.accno ) as smaller

from #test1 y

group by accno

) z

join nums n

on n.n <= z.dupes

/Kenneth

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

Hmmmm... that just made me realize that we're all forgetting something.... none of the SELECTs, so far, expose the Primary Key.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)