How To Make A Varchar 14 Len By Adding Leading 0's

Apr 22, 2008

Hi All,

I have a table called Table1 which has a field called ProductID (nvarchar type). The records are numeric, and each record may have different length of characters. The maximum length of character is 13. The records are like this

ProductID
7830005602
7378
64975259599
1380010072

As you can see it has records of different length.
In my SELECT a) first I want to make the ProductID length of 13 characters by adding leading zero's, if lenght of characters is already 13 then I will not add any leading zeros. b) Once all records have a length of 13 character then I want to put a dash "-" after first 8 characters. This way the final lenght of characters would be 14. I want to do a) & b) in one single SELECT statement.

So my final output for above example would be,
00078300-05602
00000000-07378
00649752-59599
00013800-10072

Any quick help would be highly appreciated.

Thanks,

Zee

View 12 Replies


ADVERTISEMENT

Adding Leading 00's

Mar 31, 2008

Hiya all,
I have a linked table between SQL and BTrieve.
I have a column that returns 1 ,2 ,10 etc.
In Btrieve the datatype is numeric and 3 digits long.

But in SQL I want 001 , 002 ,010.
Anyone got some code to programatically add 00 if 1,2,3
or add o if 10,11 or add none if 100,101

I also tried casting as in CAST(table.ID AS NUMERIC(3,0)) as Del_ID,
but SQL still returns 1,2,10 and not 001,002,010

View 3 Replies View Related

Appending Leading 0 To Existing Varchar

Jun 12, 2014

I have a table with a field called "pro_nums" and needs to have a leading zero appended to it. The field is a varchar datatype and I tried doing an update that simply said '0' + pro_num but that didn't work. What do I need to do to append this leading zero?

View 3 Replies View Related

Varchar Loosing Leading Zeros

Jul 1, 2006

I'm using varchar as a datatype and my leading zeros are chopped-off once ther data reaches my Stored Proc. The table will allow me to store the values with leading zeros if I enter them manually, but I cannot insert them via ASP/StoredProc.

Any ideas would be awesome.

View 2 Replies View Related

SQL 2012 :: Populate Number With Leading Zeros To Make It As 5 Digits

Jan 23, 2015

Logic:ensure the Docket number is 5 digits and populate with leading zeros if not.I have to check input number field is 5 digits, if not I have to populate with leading zeros to make it as 5 digits.

View 2 Replies View Related

Trim Leading Zeros From A Varchar Column?

Aug 10, 2007

 My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)
 
I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)
 
Can someone show my the sql statement for this?
 
Thanks.

View 11 Replies View Related

How To Trim Leading Zeros From A Varchar Column?

Aug 10, 2007

My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)


Can someone show my the sql statement for this?

Thanks.

View 4 Replies View Related

Adding A Leading Character To A String

Jan 27, 2006

I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.

It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.

I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).

And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.

View 14 Replies View Related

T-SQL (SS2K8) :: Varchar Datatype Field Will Ignore Leading Zeros When Compared With Numeric Datatype?

Jan 28, 2015

Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?

create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp

[Code] .....

View 4 Replies View Related

How To Make Escape Characters In Varchar

Jan 5, 2007

I am trying to use this:

INSERT INTO BizNames ( [Key], [Name] ) VALUES ( 0, 'Bob's Lumber' );

The apostrophe embedded in the name value is giving me headaches. I tried using double-quotes and [] to delineate the value but then I get complaints that a "Name" is not allowed in this context.

How do you turn the embedded characters into an escape character so they can be ignored by SQL Server and passed into the table field.

View 1 Replies View Related

Adding A Zero To A Varchar

May 21, 2008

I have a varchar variable of the format '0:00' eg. 7:05


I need to force the database to store this as 07:05

Is there a way to do this?

View 7 Replies View Related

T-SQL (SS2K8) :: Adding Two Month - How To Make Year Change

Oct 15, 2014

This statement adds two additional months to which is fine :

DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)

but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.

View 7 Replies View Related

SQL Server 2014 :: Adding Alphabet To A Number To Make It Unique

Nov 25, 2013

create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)

[Code] ....

For the somenum column I need to add alphabets to make them unique. for example

121a,121b,121c....121z,121aa,121ab,101a and so on...

View 4 Replies View Related

T-SQL (SS2K8) :: Adding Minutes (varchar) To Datetime Field

Mar 27, 2015

I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30

is there an easy way to add these together?

I don't mind if they get converted to integer as it will be used for comparison.

View 9 Replies View Related

Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS

Nov 20, 2007



I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?

View 10 Replies View Related

The Data Types Varchar And Varchar Are Incompatible In The Modulo Operator

Jan 4, 2008

I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.




Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END




And the error I kept getting is:

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18

The data types varchar and varchar are incompatible in the modulo operator.

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21

The data types varchar and varchar are incompatible in the modulo operator.

Any help is appreciated.

View 5 Replies View Related

SSIS - Importing Varchar From Access Into SQL2005 As Varchar

Nov 20, 2006

For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.

The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....



I get the following error



Validating (Error)



Messages

Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)


Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)


Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)


Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)


DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...

Any help would be appreciated.





View 7 Replies View Related

Datatype Question Varchar(max), Varchar(250), Or Char(250)

Oct 18, 2007



I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.

How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?

Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?

Should I just go with char(250) and watch my db size explode?

Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.

Any insight to this would be appreciated.

View 9 Replies View Related

Leading Zero

Dec 3, 2002

Hi over there, my first visit here and already the first question:

I need a SELECT-statement which formats all one-digit numbers with a leading zero.

IE:
1->01
2->02
7->07
10->10
11->11

View 5 Replies View Related

How To Take Off Leading Zero

Aug 3, 2004

I have fields like '0006','0007','0004'
etc. I need to convert this in 4 char field as right justify 6,7,4. How would I do that?

Another question:

fields in input file as 77,77.0,77.90,77.99,

I need to show them right justify in 10 chars field as 77.00,77.00,77.90 and 77.99
How would I do that?

Please help!!!!!!!!!

Thanks in advance!!

View 1 Replies View Related

Help With Leading Zero

Oct 3, 2005

I am not a DBA whatsoever so please bear with me ...

There is an existing SQL table with 3 fields that are to gather numeric data:

Name - Data Type - Length
1. TPID1 - int - 4
2. TPID2 - int - 4
3. BPIN - int - 4

If any of the 3 above are submitted with a zero in front (input = 01234), the zero will not show. (output = 1234)

I thought if I changed Data Type to nvarchar, the data would be read just like text and appear as entered. (I reviewed other table designs and nvarchar is the data type for other similar data.) Saved the table changes and still the leading zero does not show.

Any advice would be appreciated - thanks

View 4 Replies View Related

Leading Zero

May 16, 2008

declare @Mymonth as varchar(2)

set @MyMonth = '0' + datepart(m,getdate())

print @MyMonth



why wont the above produce 05 just 5

View 2 Replies View Related

Leading Zero

Aug 21, 2006

Hi all,

Anyone know how to transfer data from 1 or 2 characters to 3 characters.

For example we have mpc_code in source as 1, 2, 3 then target need to be 001, 002 and 003.

Then if 12, 13 then change to 012, 013. Put zero as leading space.

Thanks.

Grace

View 6 Replies View Related

Reporting Services :: Adding All Columns To Table Without Adding One By One

Sep 3, 2015

Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.

View 7 Replies View Related

Csv And Leading Zeroes

Oct 4, 2006

I'm trying to write the contents of a csv file to a table, but I am having problems with fields with leading zeroes.  Whenever I save as csv I lose the leading zeroes.  Does anybody know how to prevent this?

View 1 Replies View Related

Leading Zeros In SQL

May 6, 2008

I would like to add leading zeros in the date. Thsi is my existing procedure, it adds leading zeros, but it formats using "yyyy/mm/dd", instead of "yyyy-mm-dd"
Select
Id, Title, CONVERT(VARCHAR(10), ModifiedON, 111)
--CAST(YEAR(ModifiedOn) AS VARCHAR(4))+'-'+CAST(MONTH(ModifiedOn) AS VARCHAR(2))+'-'+CAST(DAY(ModifiedOn) AS VARCHAR(2))as ModifiedOn
From
ActiveAds
Where
Row between @startRowIndex And @endRowIndex

View 2 Replies View Related

Convert To Int But Add Leading 0

Jun 17, 2002

I have some results data which looks like below

.00
.00
1.0
13.0
6.0

I need to push it into a table that accepts whole numbers. (none of the results actually will have a decimal like .05 or 1.5, all .00 will be 0)
What I need in the results is whole numbers, but if the results are 6.00 I need 06 to go into the table. I know a straight int convert will drop the decimals, and I could use a csae to set the .00 to 00, but what would be the best way to change the 6.00 to 06?
THanks

View 1 Replies View Related

Leading Zero Filler Help Please...

Sep 20, 2005

MSSQL2000
Brain is overloaded and I'm just not getting this! Ugh! I need a field that will be exported/displayed to contain 10 characters, no spaces. The field I'm extracting is 8 characters and the numerical data (int) is any range up to that. So I have 35795 and need it to be 0000035795 but I could also have a 1057893 and will need it to be 0001057893. I tried various forms of this...

Select '00' + Right (chk_no, 8)

and it's just not correct.

Anyone have a suggestion on what I'm not seeing?
TIA!

View 14 Replies View Related

Generating A Leading Zero

May 13, 2008

Hi

I am unable to see how to generate a leading zero.



Table A

declare @TableA table ( ID numeric ,
Fruits varchar(10)
)
insert @TableA
select 1,'Oranges'
union all select 2,'Mangoes'
union all select 3,'Apricots'


ID Table A
1 Apples
2 Oranges
3 Grapes
4 Apricots


declare @TableB table ( seed numeric ,
)
insert @TableB
select 080513000448
union all select 080513000449
union all select 080513000450

Table B
seed
080513000448
080513000449
080513000450


I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case


SELECTconvert(varchar(10), getdate(), 12) +
(SELECT
CASE
WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
ELSE '000000'
END AS SEED
FROM B)
+ (row_number()
over (order by Id))
as SEED
FROM A



SEED
----
80513000451



The output which i need is

SEED
----
080513000451

rather then

SEED
----
80513000451

regards
Hrishy

View 20 Replies View Related

Int And Leading Zeros

Oct 31, 2005

can anyone tell me how to design a table that has an INT value that keeps the leading zeros???

no if i put in

0003453

i get

3453

thanks

View 12 Replies View Related

Add Leading Zero To Field Value Via SP

Aug 20, 2007

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @month which is created from my datetime param @date.

@Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @Year = right('0',1)year(@Date) is spitting it's toys out.

Thanks,
Brett

View 1 Replies View Related

Leading Zeros

Jul 26, 2007

Hi Folks,

I have a situation where I need to display an integer with leading zeros, with a defined length.
Example, 43 appears as 00043 when the length is 5 and 000043 when the length
is 6.

I tried using "=Format(Fields!DirID.Value.ToString)" with different variations to no avail.

Any ideas will be appreciated.

Regards

View 3 Replies View Related

Trimming Leading Zeros

May 31, 2007

mssql 2000, asp.net(vbscript) 
How am i able to trim leading zeros? Right now i have two values:00000005       500000010      1000000015      15..... etc...
how do i write a query where i can select an argument where 5 = 0000005?
the column with 00000005 is varchar and5 is numeric

View 4 Replies View Related







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