Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem aligning to the left

Status
Not open for further replies.
dndaughtery

dndaughtery

Programmer
Jan 25, 2006
67
US
I have a query which doesn't show any data for any of the fields which I have set to align to the left as follows


LEFT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWFSTN), ''),15)


Why is this and how can I fix it?
 
Sort by date Sort by votes
Also if it's char data you need to rtrim
Code: 
declare @RWFSTN char(20)
select @RWFSTN ='refefef'
select right(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, rtrim(@RWFSTN)), ''),15)
instead of
Code: 
declare @RWFSTN char(20)
select @RWFSTN ='refefef'
select right(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, @RWFSTN), ''),15)

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
hold on one second, aligning to the left or to the right?

replicate will push stuff to the right
ltrim will push stuff to the left

declare @RWFSTN char(20)
select @RWFSTN =' refefef'
select ltrim(@RWFSTN) as TrimmedValue, @RWFSTN as Value


Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
I need the fields with "LEFT" in front aligned to the left. Heres the whole SQL statement to help you understand what I'm trying to do:

SELECT RWGLTRT +
RIGHT(Replicate(' ', 25) + IsNull(CONVERT(VARCHAR, RWMRC), ''),25) +
RIGHT(Replicate(' ', 9) + IsNull(CONVERT(VARCHAR, RWBKR), ''),9) +
RIGHT(Replicate(' ', 18) + IsNull(CONVERT(VARCHAR, RWCK_A), ''),18) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWCK), ''),6) +
RIGHT(Replicate(' ', 8) + IsNull(replace(convert(varchar, RWCKDT, 101), '/', ''), '') , 8) +
RIGHT(Replicate(' ', 7) + IsNull(CONVERT(VARCHAR, RWCKAMT), ''),7) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, RWFEE), ''),5) +
RIGHT(Replicate(' ', 24) + IsNull(CONVERT(VARCHAR, RWDRL), ''),24) +
RIGHT(Replicate(' ', 2) + IsNull(CONVERT(VARCHAR, RWDRST), ''),2) +
LEFT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWFSTN), ''),15) +
LEFT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWLSTN), ''),15) +
LEFT(Replicate(' ', 20) + IsNull(CONVERT(VARCHAR, RWAD2), ''),20) +
LEFT(Replicate(' ', 20) + IsNull(CONVERT(VARCHAR, RWAD1), ''),20) +
LEFT(Replicate(' ', 20) + IsNull(CONVERT(VARCHAR, RWCITY), ''),20) +
LEFT(Replicate(' ', 2) + IsNull(CONVERT(VARCHAR, RWSTAT), ''),2) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, SUBSTRING(RWZIP5, 0, 6)), ''),5) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWHTL), ''),10) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWWTL), ''),10) +
RIGHT(Replicate(' ', 1) + IsNull(CONVERT(VARCHAR, RWRTCD), ''),1) +
RIGHT(Replicate(' ', 3) + IsNull(CONVERT(VARCHAR, RWRJCD), ''),3) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWSTID), ''),6) +
RIGHT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWMEMO), ''),15) +
RIGHT(Replicate(' ', 30) + IsNull(CONVERT(VARCHAR, RWMSMO), ''),30) +
LEFT(Replicate(' ', 30) + IsNull(CONVERT(VARCHAR, LTRIM(RWMSMO2)), ''),30)
FROM CertegyImportFile_T_EC
WHERE RecordType = 'DETAIL RECORD' AND RWCKAMT > 12.94
 
Upvote 0 Downvote
Field are *usually* left aligned naturally. You should be able to...

Select Left(IsNull(Convert(VarChar,FieldName), '') = Replicate(' ', 15), 15)

To Left align data, you want to put the spaces to the righ t of the data, not the left.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
That doesn't align the fields to the left though. I'm formatting a flat file and the client wants all varchar fields left formatted.
 
Upvote 0 Downvote
I am sorry I pasted the wrong code, same as the original

I guess the easiest is to convert to char 30

declare @RWFSTN varchar(20)
select @RWFSTN ='refefef'
select convert(char(30),@RWFSTN) ,@RWFSTN


convert(char(30),IsNull(CONVERT(VARCHAR, LTRIM(RWMSMO2)), ''))



or do stuff like this

declare @RWFSTN varchar(20)
select @RWFSTN ='refefef'
select right(@RWFSTN + replicate(' ',30 -len(@RWFSTN)), 30)

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
And my previous suggestion?

Select Left(IsNull(Convert(VarChar,[!]FieldName[/!]), '') + Replicate(' ', 30), 30)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I spoke too soon. I'm getting an error "Incorrect syntax near the keyword 'RIGHT"
when I add this:

right(RWCITY + replicate(' ',20 -len(RWCITY)), 20)
 
Upvote 0 Downvote
Check the line above it. Is there a comma or a plus symbol?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
How can I account for Null values?


I got an erorr when I tried :

right(RWFSTN + replicate(' ',15 -len(RWFSTN)), 15)+ IsNull(CONVERT(VARCHAR, RWFSTN), ''),15) +

in :

SELECT RWGLTRT +
RIGHT(Replicate(' ', 25) + IsNull(CONVERT(VARCHAR, RWMRC), ''),25) +
RIGHT(Replicate(' ', 9) + IsNull(CONVERT(VARCHAR, RWBKR), ''),9) +
RIGHT(Replicate(' ', 18) + IsNull(CONVERT(VARCHAR, RWCK_A), ''),18) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWCK), ''),6) +
RIGHT(Replicate(' ', 8) + IsNull(replace(convert(varchar, RWCKDT, 101), '/', ''), '') , 8) +
RIGHT(Replicate(' ', 7) + IsNull(CONVERT(VARCHAR, RWCKAMT), ''),7) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, RWFEE), ''),5) +
RIGHT(Replicate(' ', 24) + IsNull(CONVERT(VARCHAR, RWDRL), ''),24) +
RIGHT(Replicate(' ', 2) + IsNull(CONVERT(VARCHAR, RWDRST), ''),2) +
right(RWFSTN + replicate(' ',15 -len(RWFSTN)), 15)+ IsNull(CONVERT(VARCHAR, RWFSTN), ''),15) +
right(RWLSTN + replicate(' ',15 -len(RWLSTN)), 15)+ IsNull(CONVERT(VARCHAR, RWLSTN), ''),15) +
right(RWAD2 + replicate(' ',20 -len(RWAD2)), 20)+ IsNull(CONVERT(VARCHAR, RWAD2), ''),20) +
right(RWAD1 + replicate(' ',20 -len(RWAD1)), 20)+ IsNull(CONVERT(VARCHAR, RWAD1), ''),20) +
right(RWCITY + replicate(' ',20 -len(RWCITY)), 20)+ IsNull(CONVERT(VARCHAR, RWCITY), ''),20) +
right(RWSTAT + replicate(' ',2 -len(RWSTAT)), 2)+ IsNull(CONVERT(VARCHAR, RWSTAT), ''),2) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, SUBSTRING(RWZIP5, 0, 6)), ''),5) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWHTL), ''),10) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWWTL), ''),10) +
RIGHT(Replicate(' ', 1) + IsNull(CONVERT(VARCHAR, RWRTCD), ''),1) +
RIGHT(Replicate(' ', 3) + IsNull(CONVERT(VARCHAR, RWRJCD), ''),3) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWSTID), ''),6) +
RIGHT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWMEMO), ''),15) +
right(RWMSMO2 + replicate(' ',30 -len(RWMSMO2)), 30)+ IsNull(CONVERT(VARCHAR, RWMSMO2), ''),30)
FROM CertegyImportFile_T_EC
WHERE RecordType = 'DETAIL RECORD' AND RWCKAMT > 12.94
 
Upvote 0 Downvote
I'm using it in an insert:

INSERT INTO TempOutputFileCertegy_T_EC
SELECT RWGLTRT +
RIGHT(Replicate(' ', 25) + IsNull(CONVERT(VARCHAR, RWMRC), ''),25) +
RIGHT(Replicate(' ', 9) + IsNull(CONVERT(VARCHAR, RWBKR), ''),9) +
RIGHT(Replicate(' ', 18) + IsNull(CONVERT(VARCHAR, RWCK_A), ''),18) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWCK), ''),6) +
RIGHT(Replicate(' ', 8) + IsNull(replace(convert(varchar, RWCKDT, 101), '/', ''), '') , 8) +
RIGHT(Replicate(' ', 7) + IsNull(CONVERT(VARCHAR, RWCKAMT), ''),7) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, RWFEE), ''),5) +
RIGHT(Replicate(' ', 24) + IsNull(CONVERT(VARCHAR, RWDRL), ''),24) +
RIGHT(Replicate(' ', 2) + IsNull(CONVERT(VARCHAR, RWDRST), ''),2) +
right(RWFSTN + replicate(' ',15 -len(RWFSTN)), 15)+ IsNull(CONVERT(VARCHAR, RWFSTN), ''),15) +
right(RWLSTN + replicate(' ',15 -len(RWLSTN)), 15)+ IsNull(CONVERT(VARCHAR, RWLSTN), ''),15) +
right(RWAD2 + replicate(' ',20 -len(RWAD2)), 20)+ IsNull(CONVERT(VARCHAR, RWAD2), ''),20) +
right(RWAD1 + replicate(' ',20 -len(RWAD1)), 20)+ IsNull(CONVERT(VARCHAR, RWAD1), ''),20) +
right(RWCITY + replicate(' ',20 -len(RWCITY)), 20)+ IsNull(CONVERT(VARCHAR, RWCITY), ''),20) +
right(RWSTAT + replicate(' ',2 -len(RWSTAT)), 2)+ IsNull(CONVERT(VARCHAR, RWSTAT), ''),2) +
RIGHT(Replicate(' ', 5) + IsNull(CONVERT(VARCHAR, SUBSTRING(RWZIP5, 0, 6)), ''),5) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWHTL), ''),10) +
RIGHT(Replicate(' ', 10) + IsNull(CONVERT(VARCHAR, RWWTL), ''),10) +
RIGHT(Replicate(' ', 1) + IsNull(CONVERT(VARCHAR, RWRTCD), ''),1) +
RIGHT(Replicate(' ', 3) + IsNull(CONVERT(VARCHAR, RWRJCD), ''),3) +
RIGHT(Replicate(' ', 6) + IsNull(CONVERT(VARCHAR, RWSTID), ''),6) +
RIGHT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWMEMO), ''),15) +
right(RWMSMO2 + replicate(' ',30 -len(RWMSMO2)), 30)+ IsNull(CONVERT(VARCHAR, RWMSMO2), ''),30)
FROM CertegyImportFile_T_EC
WHERE RecordType = 'DETAIL RECORD' AND RWCKAMT > 12.94
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
387
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
796
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
561
Chris Miller
Chris Miller
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
694
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
206
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top