How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies


ADVERTISEMENT

Generate Random And Unique Number Within Sql ?

Dec 11, 2007

I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?

If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.

thoughts ?

View 5 Replies View Related

How To Generate 13 Digit Number From Two Data Colu

Aug 22, 2007

Hi

I have two below datacolumns

'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6

Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums


below is the sample procedure I am using


CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)

AS

Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],

--generate 13 digit number

make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'

from tablename
where ......

Advance thanks

View 3 Replies View Related

Transact SQL :: How To Get 8 Digit Unique Number In Server

May 19, 2015

How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.

Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'

Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'

View 9 Replies View Related

How To Generate Random Number For The Value In Th

Aug 2, 2007

How to create random number for the value in other colum. Please help and urgent

I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)

If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if

From tblname where Location = 'DAS'

I have around 2500 field. So when I run the query I expect the result should be
like below

Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89

I don't want to pass any parameter and I am new. Please help

View 7 Replies View Related

Transact SQL :: Set Unique Random Number

Dec 1, 2015

Lets say we have a table (tblProducts)

ID   Item             RandomNumber
-------------------------------------------
1    JEANS                      1234567
2    SHIRT                    72813550
3    HOOD                             Null
4    TROUSER               72191839
5    BLAZER                              0

I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query

select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts

So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.

View 15 Replies View Related

SQL Server 2014 :: Generate Random Number In Range

Oct 3, 2015

I have a function which generate random number in range :

CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO

If you run this script you always get result between 1 and 4

SELECT dbo.Func_Rand(4, NEWID())

BUT, in this script sometimes have no result !!!!!!! why??

SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )

View 2 Replies View Related

6 Digit Number Only

Apr 10, 2008

Hi,

I am trying to figure out and hope u guys help me.

I need a only 6 digit ID only eg

55 = 000055
405 = 000405
5544 = 005544

How i can do it in SQL server and which formula/datatype i must use?

Deaf can Do anything, except hear

View 9 Replies View Related

Datepart (to Get 2 Digit Number For The Month)

Apr 28, 2008

I want to build a string that contains today's date (without the time).

For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either

(1) 2008-04-28
or
(2) 20080428. (I always want the month to be represented by a 2 digit number)

Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.

So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)

The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.

Thanks for looking.

View 11 Replies View Related

Transact SQL :: 4 Digit Number To Add To Table

Oct 15, 2015

I am currently working on an app and have an issue with a table in the database.  The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column.  If necessary, I can change the datatype but would prefer not to.  The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000.  I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?

View 9 Replies View Related

How To Select Part Of Mobile Number From Digit

Sep 12, 2014

There is column mobile_number in my table

I have to replace with star(*) to the middle six character from contact number of 10 digit.

For example

The Contact number is 9334459875

I have to display it as follows

93******75 first two digit and last two only.

View 1 Replies View Related

Generate Random Numbers

Dec 12, 2006

Hi, how do I use the Rand Function to generate a Random integer value between 1 and 20. Any help will be appreciated.
 Thanks

View 1 Replies View Related

Help!!!!to Generate Random Data....

Jul 8, 2007

Dear All,
I have some problems with the assignment from my professor.

Suppose there are two tables storing first names and last names, one table with column of first name and gender, the other with last name.

how to write stored procedures to get a random first name + random last name for Males or Female. like you got a list of all M & F first name and last name, then you try to match first name with any other last name (I believe this is random).

and the task is to write a script so each time when a female or male full name is need, it will return a random name for the requested gender.


I am totally struck and no idea how to deal with it, in fact I am just a beginning in SQL server, and I know there is a stored procedure to generate random number, however, I can't see the use of it here,,,,:eek:

Could anyone help me out ? thanks very much !!!:beer:

View 2 Replies View Related

Generate Random Characters

Aug 20, 2013

I need to generate random strings each of length 7 characters and update it in a table that contains employee data.For example. There is table called as Employee. It has Id, FirstName, LastName & Email as the columns. I need to mask the real names. So i have to generate random characters between a-z unique for individual records and update it in the table.

Input table

IDFirstNameLastNameEmail
1GlenJacksontest1@email.com
2ChristopherMcClaintest2@email.com
3Carol Kholertest4@email.com
4JamesDesototest5@email.com

Output table should be:

IDFirstNameLastNameEmail
1KhjkltgErstlkjtest1@email.com
2AsdlkfuAlsdkjftest2@email.com
3AlsdkflAldkjfltest4@email.com
4BasdfweAsldkfjtest5@email.com

I went through some websites for generating random characters. These provide a 7 or 8 character random letters. URL...But i do need to update 2 columns of an entire table with random characters.

View 4 Replies View Related

Generate A Random Int To Add To A Record

Jul 20, 2005

I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?

View 1 Replies View Related

Unique Random Row Id

May 4, 2006

I once came across a database in which when ever a row was inserted, a unique random code was also inserted in a column. This code generated was similar to format like {8A5FE5F1-6BDA-476C-A4DB584A7710FBF91121054625}. I was told that SQL2000 has some inbuilt functionality for this.

Any idea on how to do this using SQL2000 or coding.
I feel this is much better than Identity Column as using autonumbers in quesrystring is too much risky as they easily readable.

Please help.
Thanks

View 1 Replies View Related

How To Generate Random Default Value For A Field Under Sql Table?

Oct 13, 2005

hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).

Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field.  I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).

Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database. 
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.

My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?

TIA

View 5 Replies View Related

Create A Unique Random Integer

Oct 22, 2004

Hi there,I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key. I have written the following function, but when i call it as a default value for a field, it does not produce a unique number. CREATE FUNCTION GETNEXTID(@CURDATE DATETIME)RETURNS BIGINTASBEGINRETURN (SELECT CAST(CAST(DATEPART(YY,@CURDATE) AS VARCHAR) +RIGHT('0' + CAST(DATEPART(M,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(D,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(HH,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(SS,@CURDATE) AS VARCHAR),2) +RIGHT('00' + CAST(DATEPART(MS,@CURDATE) AS VARCHAR),3) AS BIGINT))END Can anyone help?

View 2 Replies View Related

How To Random Increment Unique Identity

Aug 28, 2007

Hi, for some reason I want to have a unique ID with a seed and random Identity increment (I want an ascending ID's but without able to know how many objects there are).

any ideas?
thanks in advance

View 12 Replies View Related

Random Unique Values In SQL Server 2005

Jan 14, 2008

 
Hi I am using VS 2005 with SQL 2005.
I had a Datagrid and i Need to bind data into it.
I need to show Random Unique ID's based on the Parameter i pass.
I wrote select top 5 * from Employee order by NEWID()
for this i had created a Stored Procedure as
create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end
Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......

View 6 Replies View Related

Need 2 Digit Month Returned; Not 1 Digit

Sep 5, 2001

How can I return a 2 digit month into a variable for the months Jan thru September

SELECT DATEPART(mm, GETDATE()) ---> returns 9 for September.. I need 09 returned in order to properly build my target file name...

Thx,
BT

View 2 Replies View Related

Random Number

Apr 15, 2002

Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.

create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on

declare @numbers table (number_id int identity(1,1), value int)

if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end

set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))

while (select count(*)
from @numbers) < (@upper - @lower + 1)
begin
insert into @numbers (value)
select (@upper - @lower + 1) * rand() + @lower
end

select @number = value
from @numbers
order by newid()
go

View 16 Replies View Related

Random Number

Oct 8, 2005

I need to update an integer column in my table with a random number. Here's what I've done so far (with the table name and column names changed):

UPDATE dbo.MyTable
SET Column1 = RAND() * 1000000

My question is, how come the values in my column all have the same value? I thought the RAND function gives out a random number? How can I update my table to generate random numbers?

Thanks in advance.

View 3 Replies View Related

Generate Unique Code Help

Nov 6, 2007

Hi Guys,

I am trying to insert a unique code, like a voucher code, into a table in MSSQL, 10 numbers (0-9) which obviously needs to be unique.

I can code this in ASP, but I think the process could be a bit long winded, i.e. create a key - run a stored procedure to see if it is free if not loop and check again. Surely there would be a way to do this in a stored procedure in one?

So the code would look like 3928492834.

1. Generate Key in SQL
2. Check if not used in column 'kcode' if it is, regenerate and check again.

Any advice would be greatly appreciated, I know how to do this in ASP but bit of a SQL newbie!

Thanks

John

View 2 Replies View Related

Generate Unique Primary Key

Aug 17, 2007

Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?

thank you

View 5 Replies View Related

Generate Unique Numbers

May 1, 2008

Hi

Hi

I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

Something like table A has


Code:

FRUITS
-------------
Oranges
Mangoes
Apricots
....

I want to write a sql query that does something like

SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A

so the output is


[code]
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
[code]

is there a way to do this without resorting to identity columns

View 3 Replies View Related

How To Generate A Unique Barcode?

Jan 25, 2008

Hy all,

I'm working on a final project for school. It is a warehouse application. For this application I will need to generate a barcode. I have read some articles online and found out that I could use the code 39 barcode font.

I just don't fully understand how to generate the unique barcode. Could someone please help me? I also would like to store the barcodes in a sql server table. Could I use reports to print the barcode?

View 2 Replies View Related

Update To A Random Number?

Nov 27, 2004

Hi,

How can I update my users table and set the password field to a random number or string.

eg.

update tbl_users set password='a way to generate a random number say between 2000 and 100000 for example'

even better would be a randomly generated alphanumeric string.

All help much appreciated.

Cheers,

RG

View 2 Replies View Related

Random Number Generator

Feb 23, 2005

i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily.
I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column.
Will appreciate help on this

Thanks

View 1 Replies View Related

Random Number-9 Digits Only

Mar 25, 2008

Hi,

I need to generate random numbers that will always contain 9 digits.
How can I do that?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related

How To Gnerate A Random ID Number

Jun 9, 2007

I just wana make a random id number based on4 digits-for examples??Thanks in AdvanceCh.Yallin

View 21 Replies View Related

Random Number Generator

Sep 28, 2007

In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated.
I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.

Regards,

Polar Bear

View 3 Replies View Related

Generate Unique Numbers For Each Record

May 1, 2008

Hi

I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

Something like table A has



FRUITS
-------------
Oranges
Mangoes
Apricots
....



I want to write a sql query that does something like

SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A

so the output is


my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....


regards
Hrishy

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved