Adding a number to a string to create series

Question

Post reply

Adding a number to a string to create series

Meatloaf

SSCrazy

Points: 2564
More actions
September 3, 2014 at 9:38 am

#397292

Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Quick thought, multiply the EventID with 10^(num digits) and add the digits (sub ids)

😎

rxm119528 SSCrazy Points: 2960 More actions · Answer 2

declare @eventid table(Eventid int)

insert into @eventid values(31206),(31206),(31206),(31206),(31206),(31207),(31207)

select CAST(eventid as varchar(12)) + CAST(ROW_NUMBER() over(partition by eventid order by eventid) as varchar(12)) from @eventid

as d_eventid

Meatloaf SSCrazy Points: 2564 More actions · Answer 3

Meatloaf

SSCrazy

Points: 2564

September 3, 2014 at 10:49 am

#1742909

awesome! thank you!!!

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

A non-typecast version

😎

USE tempdb;

GO

declare @eventid table(Eventid int);

insert into @eventid values(31206),(31206),(31206),(31206),(31206),(31207),(31207);

SELECT

E.Eventid

,E.Eventid * POWER(10,2) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS New_Eventid -- two digits

FROM @eventid E

Results

Eventid New_Eventid

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

31206 3120601

31206 3120602

31206 3120603

31206 3120604

31206 3120605

31207 3120706

31207 3120707

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

CELKO (9/3/2014)
I have an EventID that may have many sub events. ..How can I check what the EventID is, then concatenate a sequence number by the EventID?
Why are you trying to catenate a string? Is the sub-event really a sequence in the data modeling sense? If so, then why not use a new column and use CREATE SEQUENCE so you have have each new member of the sequence handled by the SQL engine as it is created.

It's a YACK, yet another combined key

😎

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

CELKO (9/3/2014)
It's a YACK, yet another combined key
I like that initialism! Put it together, then pull it apart over and over and over ..

Spot on! The subject is a form of relational initialism, which has a comic resonance in urban jargon; down the yack then yack,yack,yack and then yack

(in English, drink up the cogniac, chat away and then vomit)

😎

Dwain Camps SSC Guru Points: 86958 More actions · Answer 7

Meatloaf (9/3/2014)
For example, I have an EventID that may have many sub events.

Like, so by many, you mean more than 9?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?

What happens if you have more than 9 an what happens if the base number in more or less than 5 digits?

I agree with the statements and questions by some of the others... Why do you want to do such a concatenation? Whatever the case, it seems like a really bad idea especially since it can cause duplicates if a 5 digit number has a sequence over 9 and 6 digit numbers are also present and receive single digit sequence numbers.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

Here is a quick demonstration of the formula I posted earlier (EventId * 10^(number of digits) + sub-event-id)

I am by no means recognizing this as a good practice, this is only a demonstration of the technique.

😎

USE tempdb;

GO

DECLARE @SAMPLE_COUNT INT = 100; -- Number of sub-events

DECLARE @eventid TABLE(Eventid INT);

;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)

,(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SAMPLE_COUNT) ROW_NUMBER() OVER

(ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)

INSERT INTO @eventid (Eventid)

SELECT 3330 -- The Main Event

FROM NUMS NM;

SELECT

E.Eventid

,E.Eventid * POWER(10,(SELECT LEN(CONVERT(VARCHAR(10),COUNT(Eventid),1)) FROM @eventid))

+ ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS New_Eventid

FROM @eventid E;

Result snip

Eventid New_Eventid

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

3330 3330001

3330 3330002

3330 3330003

3330 3330004

3330 3330005

3330 3330006

3330 3330007

3330 3330008

3330 3330009

3330 3330010

3330 3330011

...................

3330 3330095

3330 3330096

3330 3330097

3330 3330098

3330 3330099

3330 3330100

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what the OP is going to use this for. I really hope it won't cause some damage down the line for the company that the OP works for.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 11

Jeff Moden (9/4/2014)
I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what the OP is going to use this for. I really hope it won't cause some damage down the line for the company that the OP works for.

To clarify, what I consider bad practice is the combining of the keys, hence the warning. The technique has it's proper use in other applications

😎

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Eirikur Eiriksson (9/4/2014)
Jeff Moden (9/4/2014)
I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what the OP is going to use this for. I really hope it won't cause some damage down the line for the company that the OP works for.
To clarify, what I consider bad practice is the combining of the keys, hence the warning. The technique has it's proper use in other applications
😎

I agree that the technique has it's proper use in other applications and that you gave a warning but, if the warning isn't heeded, it could be damaging to a company somewhere. Maybe it's just me but I hope we didn't just cause a problem by posting the right answer to the wrong problem.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 13

Jeff Moden (9/5/2014)
Eirikur Eiriksson (9/4/2014)
Jeff Moden (9/4/2014)
I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what the OP is going to use this for. I really hope it won't cause some damage down the line for the company that the OP works for.
To clarify, what I consider bad practice is the combining of the keys, hence the warning. The technique has it's proper use in other applications
😎
I agree that the technique has it's proper use in other applications and that you gave a warning but, if the warning isn't heeded, it could be damaging to a company somewhere. Maybe it's just me but I hope we didn't just cause a problem by posting the right answer to the wrong problem.

I recognize that I should have been clearer on the warning, thanks Jeff for highlighting the potential issue.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?

If you pursue this, you will destroy your EventID key. Matching to EventID in any other table will require that you identify which part of EventID is the EventID and which part is the subeventid. If you want to have a subeventid somewhere then create a new column for it. Having said that, creating sequences and subsequences using ROW_NUMBER() is so trivially easy that perhaps you should be evaluating subeventid in code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden