Right align (format) Text in SQL

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),' ')))
YVR33Asked:
Who is Participating?
 
CboudrozConnect With a Mentor Commented:
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
 
PacaneCommented:
You may use the format() function..
See the use: http://www.w3schools.com/SQl/sql_func_format.asp
0
 
YVR33Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.