column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.