Convert interger to string

Question

Post reply

Convert interger to string

sql_novice_2007

Hall of Fame

Points: 3307
More actions
September 7, 2011 at 12:23 am

#242846

SELECT CAST(ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) as varchar),ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0)
When I run the above query I am getting the following results.
5.39524e+007 ,97819793
How can I get result of CAST(ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) as varchar) as 53952466 instead of 5.39524e+007 ?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

SELECT ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) ,ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0)

Is this something you are looking for? Why do you want to cast as varchar? Any special reason?

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

sql_novice_2007 (9/7/2011)
SELECT CAST(ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) as varchar),ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0)
When I run the above query I am getting the following results.
5.39524e+007 ,97819793
How can I get result of CAST(ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) as varchar) as 53952466 instead of 5.39524e+007 ?
Thanks.

You need to cast to INT before casting the FLOAT result to VARCHAR...

SELECT CAST(CAST(ROUND(((99999999 - 1000 -1) * RAND() + 1000), 0) AS INT) as varchar(8))

If you don't mind, I'd like to know what the business reason is for doing this just because I'm curious.

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

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 3

I need this because this random number should be concatenated with a string.

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

Then there's a much better and faster way to do it. You need random numbers from 1000 to 99999999, right?

SELECT CAST(ABS(CHECKSUM(NEWID())) % 99998999 + 1000 AS VARCHAR(8))

The cool part about the code above is that you don't need a loop if you need more than one...

SELECT TOP 10000

RND = CAST(ABS(CHECKSUM(NEWID())) % 99998999 + 1000 AS VARCHAR(8)) + ' Is a random number'

FROM sys.all_columns ac1,

sys.all_columns ac2

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

nellisjp SSChasing Mays Points: 624 More actions · Answer 5

Keep in mind that the RAND() function is only pseudo-random. If you need truly random numbers, I'd go with another technique - perhaps utilizing GUIDs?

Edit: Jeff beat me to it.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

nellisjp (9/8/2011)
Keep in mind that the RAND() function is only pseudo-random. If you need truly random numbers, I'd go with another technique - perhaps utilizing GUIDs?
Edit: Jeff beat me to it.

Nope... GUIDs are Pseudo-Random as well. Within the small set of random numbers most people need compared to the rather large domain of random GUIDS, they're mostly not predictable, though... especially since most people don't know what the first GUID used is.

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