Generate random varchar

1

I am using the following code to generate 4 number randomly
declare @RandNumber VARCHAR(4) = convert(varchar(4),convert(int,rand()*10000))

the numbers need to be inserted into a field varchar

Somehow one of the numbers that were generated only had 3 characters.
How can I make sure that there are always 4 , never less?

Thanks

2 
declare @RandNumber VARCHAR(4) =
    RIGHT('0000'+CONVERT(varchar(4),convert(int,rand()*10000)),4)

RAND function is really pseudo-random in the sense that it generates the same sequence of numbers if you start with the same seed. If you want truly random, you might consider using newid() function. For example ABS(CHECKSUM(NEWID())) % 10000

3

Slightly different take on this for 1 or a million values...

--===== Declare the variable and populate it in the same line of code.
DECLARE @RandomNumber CHAR(4) = RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4)
;
--===== Pretty easy to make a million of them, as well.
 SELECT TOP 1000000
        RandomNumber = RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4)
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
4

hi

know this topic was 9 months ago

I was trying to figure out what happened ????
:thinking:
:thinking:

My observation is
rand() function returns random values
sometimes
0.02333
sometimes
0.24568

Observe the 0 after the .

so when you do
convert(int,rand()*10000)
it returns 3 numbers or 4 numbers

when rand() = 0.02333 then convert(int,rand()*10000) = 233
when rand() = 0.2333 then convert(int,rand()*10000) = 2333

this the reason why when you convert to varchar
3 characters ALSO appear when you want 4 characters

:grinning:
:smile:

5

What is it that you're trying to figure out? There are a couple of solutions that do things as requested. They also work for entire columns where just RAND() without the proper seed won't.

6

hi jeff

I was "just" trying to
put my observations
:slight_smile:
:slight_smile:

I saw in the posts that no had explained what was the reason !!!

i am aware there are solutions
I was also working on giving another solution

But did not quite get there
:upside_down_face:

7

Ah... got it. Thanks for the feedback.

If you're really interested in a bit of info on the subject, see the following articles. The ability to quickly generate lots of random but constrained data is largely what has gotten me to where I am today, especially when it comes to performance tuning in T-SQL and exploring new methods of doing things. 10 rows just isn't good enough for such things..
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

8

Nice Jeff

Performance Tuning
when actual Data is only 1000 rows
is very tough

In Performance Tuning
My experience has been
It takes a different mind set
Stuff like Lots of Temp tables etc etc
Logical Reads .. Reducing
And in companies I do not have "permissions"

Just wanted to share my "Two Cents"
:slight_smile:
:slight_smile:

9

I agree that performance tuning when the actual data is only 1000 rows is tough. That's why I use the techniques in the two articles I post to usually test for 1 Million rows.

10

Jeff

Also the other thing in my experience is

Data breaks code

So testing .. i mean generating data with
all kinds of scenarios in mind
would help

In my opinion

:slight_smile:
:slight_smile:

11

Absolutely correct. There are "Devils in the Data" and that's why I like QA people a whole lot. They come up with things that I can only imagine.

And that's why I wrote the two articles I provided links to. If you put a couple of things together using those techniques, you can test a shedload and it's fairly easy to come up with all sorts of scenarios.

12

Jeff

took a look at those articles

looks like you are into SQL Server Development
A LOT

:slight_smile:

13

Heh... you might say that. I'm the guy that created the term "RBAR" and popularized the name "Tally Table" instead of calling a table of sequential numbers a "Numbers Table".

14

Jeff

Who am I talking to ?
WOW

I am just a novice

Nice to meet you
:slight_smile:

15

Jeff is a legend. We are not worthy

16

@harishgg1

A pleasure to "meet" you, as well. And, to be sure, we all started out as novices. I had SQL Server thrust upon me back in '96. I was truly an "accidental DBA" and, although it was "fun", it was trying (heh... still is, sometimes). There's so much to learn about SQL Server. While I'm pretty darned good at T-SQL and other things like indexes and the like, I'm still a rank novice in a lot of areas.

That's all a part of the reason why I taught myself how to build shedloads of test data. I found a website way back then called "Belution.com" and was a fairly active member. A lot of what I saw, when it came to issues of performance, was based on hear-say, opinion, and "Best Practices" that actually turned out to be not so good and so decided to "let the code do the talking" for me. Unfortunately, Belution.com went offline more than a decade ago.

With sites like this one (SQLTeam.com) and the good people (there are some serious heavy hitters on this and other forums), it's pretty easy to find new and sometimes better ways even for an old dog like me.

17

Thank you for the very kind words but... we're all in this together... everyone is "worthy". Not sure about being a "legend" either. Like Brent Ozar says, I've just made more mistakes than a lot of people have and can sometimes help others avoid them.

18

Yosiasz

Gotcha I think

Looks like u r from u.s of A
Also

:grinning: