Concatenate numbers to form a 7 digit string

Question

Concatenate numbers to form a 7 digit string

Viewing 14 posts - 16 through 28 (of 28 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

True - but I cust those chances in half with concatenating two of them together. Still not entirely gone I suppose, but probably good enough for government work:)

Of course - hooking your solution up to the cross join also seems to work just fine as well.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Michael Valentine Jones (2/12/2008)
John Beggs (2/12/2008)
I think you'll find that the difference between 1ms and 1ms is pretty negligible. Feel free to test for yourself.
What boundary would you set for flexible coding versus running faster? 10ms? 100ms? 1000ms?
Personally, for no measurable difference, as in this case, I'll go for flexible.
When I put both solutions into a query window and asked it to showplan, your solution showed as 100% of the batch, so that would be a 100 to 1 or greater difference in performance.
Performance needs depends on the application needs. Is it being called 10 times per second? 100 times per second? 1000 times per second?

Odd to see you say such a thing about "100% of batch", Michael... you and I both know that that particular measurement has absolutely nothing to do with what the performance will be... even the Actual Execution Plan occasionally lies in that area....

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

I don't think it's a stretch of the imagination to believe that a query that is a cross join (probably unnecessary, since you only need 7 rows) between system views that are actually joins between two hidden system tables will take much longer than a scaler assignement.

The fact that they are both fast does not mean there are not orders of magnitude difference in performance.

I think that if you really want to test it, you would have to run a test that did both millions of times. I don't feel motivated to do that.

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

I'm confused... I believe the cross-join is to generate a million rows... not to generate the 7 digits. Perhaps I was looking at the wrong post. Which one were you talking about, Michael?

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

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

stricknyn (2/12/2008)
Hello,
Seems like this should be pretty straight forward, but its not working. I'm trying to build a string with 7 characters from a random statement:
Declare @mn int
Declare @mx int
Declare @intCounter int
Declare @textNumber varchar(7)
Set @mn = 1
Set @mx = 9
While @intCounter <= 7
Begin
Set @textNumber = @textNumber + Cast(Round(@mn + (RAND() * (@mx-@mn)),0) as varchar(1)) --Generates a random number from 1 to 9
SET @intCounter = @intCounter + 1
End
SELECT @textNumber
Basically trying to build a string like 4567345 for example BUT @textNumber isn't returning anything. Can anyone see anything that doesn't look right?
Strick

Just curious... why is it that you don't want the digit "0" anywhere in the 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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Jeff Moden (2/12/2008)
I'm confused... I believe the cross-join is to generate a million rows... not to generate the 7 digits. Perhaps I was looking at the wrong post. Which one were you talking about, Michael?

I was talking about the first post from John Beggs at 3:27 PM

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

Hello!

Would something like this work for you?

SELECT RIGHT('0000000' + LTRIM(STR(RAND()*10000000)),7)

Best Regards,

Chris Büttner

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

Chris,

Good one... Keep in mind that would be good for one row at a time (Rand is not random in a single query) and if the digit "0" were allowed... Stricknyn's orginal post (1-9) seemed to indicate that a 0 in the string was not permissible.

I'm still waiting to hear from Stricknyn as to whether the digit "0" is allowed in the mix... then we'll make a million of the buggers in something like 1.8 seconds or so... a bit faster than the 1ms/per that other folks were talking about...

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Jeff Moden (2/13/2008)
Chris,
Good one... Keep in mind that would be good for one row at a time (Rand is not random in a single query) and if the digit "0" were allowed... Stricknyn's orginal post (1-9) seemed to indicate that a 0 in the string was not permissible.
I'm still waiting to hear from Stricknyn as to whether the digit "0" is allowed in the mix... then we'll make a million of the buggers in something like 1.8 seconds or so... a bit faster than the 1ms/per that other folks were talking about...

Well, yeah, but the 'no zeroes' is what's making it not straight-forward....at 5 seconds for my million row solution (and interestingly enough, for Michael's solution when hooked up to a cross-join :)), I didn't think we were doing so bad....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Sorry guys... didn't mean any offense... today's cold medicine still hasn't worn off and it came out all wrong 😛

I guess what I really wanna know from Stricknyn, is are "0"'s allowed and, if not, why not? Always interested in another person's reasoning and, depending on his answer, I might be a day late and 5 seconds short 😀

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

Hm, sometimes I wish I would be reading the posts more thoroughly;-)

In any case, here is something without zeros:

SELECT

CAST(CEILING(RAND() * 9) AS char(1))

+CAST(CEILING(RAND() * 9) AS char(1))

Of course I am still assuming this is not generated against a table but simply as a onetime calculation.

If I am not totally off the road, this should give a more even distribution of values than the checksum method. But this is probably not that important.

Time for me to take my medicine too... I want to tackle the 441 exam this Friday and hope I will be healthy again by then!

Best Regards,

Chris Büttner

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Jeff Moden (2/13/2008)
Sorry guys... didn't mean any offense... today's cold medicine still hasn't worn off and it came out all wrong 😛
I guess what I really wanna know from Stricknyn, is are "0"'s allowed and, if not, why not? Always interested in another person's reasoning and, depending on his answer, I might be a day late and 5 seconds short 😀

No offence taken - just setting the bar in case you decide to throw your hat in the ring....:)

Say - you're not mixing cold meds and nicotine cessation products, are you? If you are - say hi to the Pink elephants for me....:crazy:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Christian Buettner (2/13/2008)
If I am not totally off the road, this should give a more even distribution of values than the checksum method.

Actually, Matt and I beat the daylights out of the checksum method a couple of months ago... the distribution on the checksum(NewID) method is just as good and the distribution on a Rand method. I know... I was skeptical at first, as well, but I'm the one that did the testing...

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

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

Matt Miller (2/13/2008)
No offence taken - just setting the bar in case you decide to throw your hat in the ring....
Say - you're not mixing cold meds and nicotine cessation products, are you? If you are - say hi to the Pink elephants for me....

Not just yet... I gotta get the OP to tell me what color the bar is before I do the ring toss with ya 😛

So far as the Pink Elephants go... nah... just the heavy cold medicine... the elephants haven't turned to pink, yet. :hehe:

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