Solved

Right align (format) Text in SQL

Posted on 2010-11-09
3
1,399 Views
Last Modified: 2012-05-10
When there is no value for “test value” or “test unit”, I would like to have the Date align to the right to be under the Date field with values.  I have tried to use the rtrim and the char spacing and this did not work.  I would like to have some suggestions on this.  Thanks
I am getting the following now:
3.6 K/mcL          Date: 12/18/2008
--->      Date: 10/09/2008
11.1 g/dl - 06/26/2009
Unk
Would like to see:
3.6 K/mcL          Date: 12/18/2008
                          Date: 10/09/2008
11.1 g/dl - 06/26/2009
Unk
CASE WHEN dbo.FN_dmthem_GetLookupDesc('sourceCd',sourceCd) = 'YNCC'
         then  cast(cast(testvalue as float) as varchar)+' ' +testUnit+' - '+isnull(convert(VARCHAR(15),testDte,101),' ')  
     WHEN dbo.FN_dmthem_GetLookupDesc('sourceCd',sourceCd) = 'Unk'
       then convert(VARCHAR(20),dbo.FN_dmthem_GetLookupDesc('sourceCd',sourceCd))
    Else isnull(cast(cast(testvalue as float)as varchar) + ' ' + testUnit,' '  ) +' ' + isnull('      Date: '+ convert(VARCHAR(15),testDte,101),' ')

Tried:
replace(replace(replace(isnull('    Date: '+ convert(VARCHAR(15),testDte,101),''),char(9),''),char(10),''),char(13),'')
Also Tried:
+ ltrim(rtrim(isnull('      Date: '+ convert(VARCHAR(15),testDte,101),' ')))
0
Comment
Question by:YVR33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Expert Comment

by:Pacane
ID: 34095250
You may use the format() function..
See the use: http://www.w3schools.com/SQl/sql_func_format.asp
0
 
LVL 7

Accepted Solution

by:
Cboudroz earned 500 total points
ID: 34095434
you need to know in which position you want it.

like this all the date will start in position 20


CASE 
 WHEN CONDITION1 = 1 THEN CONVERT(CHAR(20), isnull(data1,'') ) + date
 WHEN CONDITION2 = 2 THEN CONVERT(CHAR(20), isnull(data2,'') ) + date
ELSE CONVERT(CHAR(20), isnull(data3,'') ) + date
END

Open in new window

0
 

Author Comment

by:YVR33
ID: 34103248
This did not seem to work, I got the following results:
The "Date" in the Else is what I  would like to have aligned.  The value and unit maybe null and when this occurs I would like to have the "Date" label aligned to the right.

Else isnull(cast(cast(testvalue as float)as varchar) + ' ' + testUnit,' '  ) +' '+ + isnull('      Date: '+ CONVERT(VARCHAR(50), isnull(testDte,'') + testDte, 101), '')

Open in new window

EE-Date-Value-scrprnt.docx
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question