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 Chriss Miller 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
Joined
Jan 25, 2006
Messages
67
Location
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
Why are you doing this then?
LEFT(Replicate(' ', 30)

give me an example what you need in this format
value now ' dfdfdf'
desired 'dfdfdf'
is that what you want?

or do you want this
value now ' dfdfdf'
desired 'dfdfdf '

Denis The SQL Menace
SQL blog:
Personal Blog:
 
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
I need it to be as such:

'dfdfdfd8 '

30 positions
 
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 Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
889
Andrzejek
Andrzejek
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
889
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
Seconds to minutes
Replies
5
Views
2K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question Question
fill the gap of hierarchy data
Replies
11
Views
1K
Chris Miller
Chris Miller
dgillz
  • Locked
  • Question Question
How to convert varchar to time?
Replies
1
Views
1K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top