August 30, 2010 at 3:38 pm
Hello All,
This is my problem.
Column Name: CdLstName
Column Size: varchar(30)
The final output from this file will be transformed into a text file.
If i insert a last name of size 7 and then export it to an excel it will automatically start the next column on the next position. However i want 23 spaces there.
I figured if i can find out the size of the column i could use space() and deduct that from the length of the input. But i have not been able to find a way to calculate the length of a column. Let me know if there is a better way to implement this in sql 2005.
I have already tried LEN and DATALENGTH
August 30, 2010 at 3:43 pm
The VARCHAR is causing that I believe. Can you just use CAST and make it CHAR(23)?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
August 30, 2010 at 3:50 pm
Tried doing a variety of things but it does not work.
Example:
1. SELECT LEN(cast(CdLstName as char(30))) FROM dbo.abc
2. SELECT DATALENGTH(cast((CdLstName) as char(30))) FROM dbo.eCapDetail
August 30, 2010 at 4:22 pm
chaudharyabhijit (8/30/2010)
Tried doing a variety of things but it does not work.Example:
1. SELECT LEN(cast(CdLstName as char(30))) FROM dbo.abc
2. SELECT DATALENGTH(cast((CdLstName) as char(30))) FROM dbo.eCapDetail
I tried testing this and Excel converts the column to General type. But I can tell you that if you insert the CAST(CdLastName as CHAR(30)) into another table, it will be 30 characters wide. So your test with the datalength and LEN are not going to show you what would happen. By the way, the LEN function trims trailing spaces, so it's not much help here.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply