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 RepliesHow 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
I created a trigger via the Querry Analyser and it works but I don't know where to go and see my existing trigger. I thought it would be visible from Enterprise manager but I see nothing(not to sure where to look anyway, thought it woould show in the sp but it doesnt).
Also how can I delete a trigger once it is created?
Thanks.
--------------------------------------------------------------------------------
I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:
Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348
I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.
All help would be appreciated.
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
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
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 RelatedI'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
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
how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.
View 7 Replies View RelatedI have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.
This is what I have
Code:
SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;
which does what I want it to do except it uses the whole 9 digits.
Any help would be appreciated
I have a field in a table that contains ten digit value representing a datetime. Is there any way to convert it to default datetime formatThanks--Message posted via http://www.sqlmonster.com
View 2 Replies View Relatedcreate function dbo.iso6346_char_to_number(@char char)returns intasbeginreturncase upper(@char)when '0' then 0when '1' then 1when '2' then 2when '3' then 3when '4' then 4when '5' then 5when '6' then 6when '7' then 7when '8' then 8when '9' then 9when 'A' then 10when 'B' then 12when 'C' then 13when 'D' then 14when 'E' then 15when 'F' then 16when 'G' then 17when 'H' then 18when 'I' then 19when 'J' then 20when 'K' then 21when 'L' then 23when 'M' then 24when 'N' then 25when 'O' then 26when 'P' then 27when 'Q' then 28when 'R' then 29when 'S' then 30when 'T' then 31when 'U' then 32when 'V' then 34when 'W' then 35when 'X' then 36when 'Y' then 37when 'Z' then 38endendcreate function dbo.iso6346_check_digit(@string char(10))returns charasbeginreturn (dbo.iso6346_char_to_number(substring(@string,1,1) )*1+dbo.iso6346_char_to_number(substring(@string,2,1) )*2+dbo.iso6346_char_to_number(substring(@string,3,1) )*4+dbo.iso6346_char_to_number(substring(@string,4,1) )*8+dbo.iso6346_char_to_number(substring(@string,5,1) )*16+dbo.iso6346_char_to_number(substring(@string,6,1) )*32+dbo.iso6346_char_to_number(substring(@string,7,1) )*64+dbo.iso6346_char_to_number(substring(@string,8,1) )*128+dbo.iso6346_char_to_number(substring(@string,9,1) )*256+dbo.iso6346_char_to_number(substring(@string,10,1 ))*512)%11end
View 1 Replies View RelatedI 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.
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 Relatedhi 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
I am retrieving the current month and setting a variable accordingly.
Code:
DECLARE @currMonthNum INT
SET @currMonthNum = DATEPART(MM, GETDATE())
My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).
Here is my attempt to do this...!
Code:
IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.
I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).
how to display a zero in front of the months that require it?
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.
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
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
Using SQL Analyzer or .NET 2.0 programmatically, is there a way to get Transact SQL 2000 to not append a digit at the end of an identifier name when the result set has the same identifier multiple times (from a JOINed SELECT to various tables which have names in common and where I need to get all column names/values in one fell swoop)?
SELECT A.*, B.* from TableA A
INNER JOIN TableB B on (A.Name = B.Name)
The result set will have NAME1 and NAME2, of course (or perhaps it's NAME and NAME1 -- no matter -- I wish to get rid of the numeric suffic).
Thanks much in advance.
I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.
SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')
This works perfect when running in T-SQL, but when attempting to define a variable in the expression
REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?
Anyone run into a simliar issue or have a suggested work around?
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
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'
How to convert 6 digit number to mm/yyyy.
View 5 Replies View RelatedWe are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.