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


ADVERTISEMENT

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

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 Update,delete,insert The Data Into Xml Colu

Oct 10, 2007

Hi all,

How to update a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

How to DELETE a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

how to INSERT a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

update XmlCatalog1 set Document1.modify('delete /X12_U1_837/X12_Q1_837/header/ISA//ISA_Authorization_Information_Qualifier') where id=2

----------
The error which i am getting is
XML Validation: Invalid content. Expected element(s):ISA_Authorization_Information_Qualifier where element 'ISA_Authorization_Information' was specified. Location: /*:X12_U1_837[1]/*:X12_Q1_837[1]/*:header[1]/*:ISA[1]/*:ISA_Authorization_Information[1]

View 1 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

How To Select Data If Length = 12 Digit

Mar 29, 2008

hi everyone,

I'm looking for statement how to select if length digit = 12. as an example,

data
---------------
123456789012
4567892100

I just want to select if the length of data = 12 digit then the answer is 123456789012

Your kindness really appreciated, I really need the answer as quickly

View 3 Replies View Related

Two Digit Year Format - Loading Data

Apr 20, 2006

I have run into something really annoying.
If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')

But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.

I know there is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049. Is there anything similar within SSIS?

Thanks
Sutha

View 3 Replies View Related

Generate Sequence Number In Sql???

Mar 15, 2005

Hi,
I have written the following StoredProcedure





Code:

create Procedure spCreateQuestion(
@QuestionName varchar(30)
)

as

declare @newAnswerId int
declare @newQuestionId int

set @QuestionName='New Question'

BEGIN TRANSACTION Q1

--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions

--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName

BEGIN TRANSACTION QA1

--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers

--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this

--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId

COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1



I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId

i can't use the identity column in my tables

Can some one please have a look at it and suggest me how do we go about it..

View 8 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

Forcing AutoNum To Generate A Number

Oct 4, 2001

Hello all,
Is there any way to force Autonum to generate a number before an entire record is created? Some of my forms will not work because it needs a number already listed in its index (which uses Autonum) and cannot add to the table until it is created.I really need it to have a number ready and waiting upon the last record's completion.

View 1 Replies View Related

Generate Number From SELECT Statement

Jul 30, 2002

Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA


Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1

View 1 Replies View Related

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

Generate Sequence Number Basing On Each Id

Mar 28, 2008

Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC


Expected results as below :
------------------

Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2

Thanks in advance

View 4 Replies View Related

How Can I Generate Incremented Linear Number T-SQL

May 26, 2008

Hi all




i wants to generate linear sequence number like 1,2,3,.............1000000


,are there any function like NEWID() ( this return unique guide, i want to get integer)




i want to used this generated number inside the SQL query




thanks

IndikaD (Virtusa Cop)

View 12 Replies View Related

Finding Differences Between Dates In A Single Colu

Feb 28, 2008

I have a log table that displays the history of the status of an entity and the date the status changed.

member_id | modification_date | status

I wish to find the differences between datetime values in different rows of the modification_date column. I would need the amount of time between whatever the next sequential time was for the member. I would also need to know the status change in that time. I don't know how to do this with just one date column.

Any assistance is appreciated.

Thank you.

View 2 Replies View Related

How Do I Generate Auto Increment Number In SQL Express?

May 22, 2006

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?

In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?

Thanks a lot.

View 2 Replies View Related

T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

I'm trying to do a simple insert into a table, something like this:

insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)

So, the SELECT would generate something that looks like this:

ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)

Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:

ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)

What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.

Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.

What I'm hoping to do is rewrite my SQL like this:

insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'

Is there an easy way to do this?

View 9 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

SQL Server 2008 :: Generate Increment Number With Conditional Restart

May 22, 2015

I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.

What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.

CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),

[code]....

View 8 Replies View Related

Erasing Digit

Feb 18, 2007

Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem? Thank you

View 3 Replies View Related

Splitting 9 Digit Zip Codes

Jul 6, 2004

hey all
i am stuck with this little problem
I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'

is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?

thanks

View 6 Replies View Related

Build ID W/Incrementing Digit

Jul 23, 2005

Help, I'm stuck! I have a Customer table:ID Name-- ---------------------1234 Christopher's Surf Boards4321 Christina's NailsI have to build a Account table:ID GPID-- --------------------1234 CHRIST004321 CHRIST01I've built a function that gives me the six alphas and concatenates thetwo digits. The GPID in the account table is the first six alphas ofthe company name plus two digits to ensure uniqueness. (Don't ask why Ican't use the existing id -- the story is long and boring.) I built afunction that creates the alpha part and assigns the first 00. I builta second function that I thought would check the Account table as I wasinserting and increment the numbers if the account number alreadyexisted. It doesn't work. In the above table, both IDs come out asCHRIST00. Here's the second function:ALTER FUNCTION fnValidateID(@mString varchar(15))RETURNS varchar(15)ASBEGINDECLARE@sTemp varchar(15),@sInc varchar(2),@iInc intBEGINset @sTemp = @mStringwhile exists (select GPID from dbo.tempID where GPID = @sTemp)beginset @iInc = convert(int,right(@mString,2)) + 1set @sInc = convert(varchar(2),@iInc)if len(@sInc) < 2 set @sInc = '0' + @sIncset @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sIncend--NEXTENDRETURN @sTempENDMy first guess is that the code is okay, so far as it goes, but that Ican't check the values in the table at the same time I'm inserting intoit. (The Account table is empty, initially.) In any case, I can'tfigure out where I've gone wrong.Thanks.

View 8 Replies View Related

Check Digit Calculation

Nov 20, 2007

I'm needing help with the following check digit calculation.

I have a Table with the following columns

Newscan nchar(31)
checkdigit nchar (10)


Newscan ex value 012345670 600888081 307051111

I need to multiply each character by the following sequence starting on the left alternating the multiplyer skipping spaces
21212121

if the multiplyed number is greater than 9 I need to add the two digits together. Ex 2X9 = 18 for a saved value of 9

I then need to add the entire string together
divide that by 10 and take the right most character and subtract that from 10 to end up with a check digit number that I want to store in the column checkdigit.

I have a case statement that converts the alpha chars in the string to the correct numeric values but I'm lost after that on how to accomplish this.

Thanks in advance for any help or direction.

I tried to look at the bank routing check digit function but couldn't get to the code of it.

Bill

View 12 Replies View Related

Retrieving A 2 Digit Month

Nov 19, 2007



I am sure this will be an easy one for you pros, but I am having a case of the Mondays. I am trying to retrieve a date in the format 2007.01.01. In trying to do this I either end up with 1 or null when trying to create the 01 part. Below is some test code for the month portion.

DECLARE @Tst CHAR(4)
DECLARE @tst1 CHAR(2)
SET @tst = DATEPART(Month, '1/1/2008')
SET @tst1 = '00'
--SET @tst = right(@tst1 + @tst, 2)
Select @tst, @tst1, @tst1 + @tst, right(@tst +tst, 2)

Result Set:

1 00 001 [blank]

What am I doing wrong here?

TIA

View 6 Replies View Related

Generate Sample Data

Sep 18, 2005

I am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??

Thanks in advanced,

Edit:
User needs to fill his tables with sample data.
(Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).

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 Lab Statistics From Data

Oct 11, 2007

Hello,

I'm a network admin who understands sql but have no reason to write it everyday so I have no idea how to approach this problem.

I am trying to generate usage statistics of our computer labs. I have written a few scripts that populate a sql 2005 database with the following information:

[datetime] [username] [computer] [event]
2007-10-10 15:25:03 tom earth logon
2007-10-10 15:39:09 john mars logon
2007-10-10 15:41:13 dave pluto logoff
2007-10-10 15:47:29 john mars logoff
2007-10-10 15:59:48 tom earth logoff

Each time a user logs on or logs off one of our lab computers the information is inserted into this database.

It seems that was the easy part. Lacking any local sql experts, I'm pondering where to go from here to generate usage statistics.

What I'm thinking is a SQL query (which I don't know how I'd write) that would calculate the session time and insert it into a new table. A session is defined as the time between a logon and logoff where the username and computer match.

The new table would look something like this:

[user] | [computer] | [sessionTime] | [day]
tom | earth | 00:34:45 | 2007-10-10
john | mars | 00:08:20 | 2007-10-10

The logic of the query finds the first logon event and matches it with the first logoff event in which the username and computer match. It then subtracts the logon datetime from the logoff to calculate session time.

Is this a reasonable approach? Is the query easy to write?

View 4 Replies View Related

How To Use Data To Generate A Report

Jun 26, 2006

Hi,

I have one problem in my project. Generating the report plz help me.

I have one table called Emp, which consists of fields, empid,tdate,attn,reason.

Emp
----
empid | tdate | attn | reason

2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad

2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null


I want to generate a report as given below based on the date submitted by the user.

Frm Date: 6/3/2006 To Date: 6/4/2006

empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1


Please write the query and reply back to me. ASAP.

Thanx in advance.

View 3 Replies View Related

Generate Script With Data

May 10, 2007

Hi,

Can Anyone tell me how to generate script with all table data from one database.I can generate script with table structure but not with table data.I want to generate with Table data.



Thanks,

Pavan

View 4 Replies View Related







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