Help with keeping the leading zeros

Question

Post reply

Help with keeping the leading zeros

JasonYousef

SSCommitted

Points: 1769
More actions
April 12, 2010 at 10:08 am

#386682

Hello,
I run this query Select *
into dbo.ReportFinal
from
(
SELECT A.MedRecNo,
A.AdmitDate, B.AdmitDate AS ReAdmitDate,
A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge,
A.PatientNo, B.PatientNo AS RePatientNumber,
A.PatientName,
A.DXCode, B.DXCode AS ReAdmitDXCode,
A.LOS, A.DIAGName,
A.DrgNo, B.DrgNo AS ReAdmitDrgNo,
A.FinCode, A.FinDescr
FROM report A
INNER JOIN report B ON A.MedRecNo = B.MedRecNo AND
A.AdmitDate <> B.AdmitDate and A.AdmitDate < B.AdmitDate
)A
the original table data type for MedRecNO is nvarchar(50) and for the new table is the same, but it keeps removing the leading zeros !
so how to override that or what am doing wrong? :w00t:
Please help, thanks

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the issue is datatypes; integers/number datatypes do not have preceeeding zeros;

i think your table dbo.ReportFinal.MedRecNO is defined as an integer;

you said it's defined as an nvarchar, but that's the only thing that makes sense, and is very common.

an implicit conversion is converting the nvarchar to integer, so "00501" becomes 501 (note no quotes...it's number now!)

if you ever had any MedRecNO that was "0501A", it would finally error out with an error saying unable to convert to int. right now, your query is not having that issue.

you'll need to change the column definition in dbo.ReportFinal to preserve leading zeros.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 2

Lowell is correct. You are converting this to a number in this table, and by convention, numbers do not include leading, or trailing zeros after the decimal.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 3

Based on what Lowell said, my recommendation is that you check the final table for the correct datatype. If you want, the CREATE TABLE statement for both tables... a million sets of eyes are better than just one.

Also, do you have any triggers on the target table? If so, please post it.

Last but not least, is the target table actually a target table or a view that updates and underlying table?

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

assumeing that the entire 50 will be prepended w/ zeros each time you can always use replicate function to put the zeros back. You didnt send desired example output so i cant really tell.

dant12 SSCrazy Points: 2712 More actions · Answer 5

as far as I know you can't "SELECT * INTO TABLE" into an already created table, so I don't think the problem is the target table definition as it doesn't exist yet. somehow, from the subquery to the table creation, the varchar(50) is turning into an Integer you could either try CAST(A.MedRecNo AS NVARCHAR(50)) AS MedRecNo to force the datatype in the new table or REPLACE(STR(A.MedRecNo,50,0),' ','0') AS MedRecNo to enforce the zeros on the left

--
Thiago Dantas
@DantHimself

JasonYousef SSCommitted Points: 1769 More actions · Answer 6

That really worked ! thanks so much and cheers for the reply and the brains....

dant12 (4/16/2010)
as far as I know you can't "SELECT * INTO TABLE" into an already created table, so I don't think the problem is the target table definition as it doesn't exist yet. somehow, from the subquery to the table creation, the varchar(50) is turning into an Integer you could either try CAST(A.MedRecNo AS NVARCHAR(50)) AS MedRecNo to force the datatype in the new table or REPLACE(STR(A.MedRecNo,50,0),' ','0') AS MedRecNo to enforce the zeros on the left