text positioning for select list I have following issue, I have hierarchy table where every record has level, code and description and I need to have description to look as it is was one column.

Example code

CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT 
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp


Output



But i need to have description to start at red line for every hierarchy record.
Any idea ?
You should use len() instead of datalength() to get the number of characters in Value. They are different because Value is a double byte string (nvarchar).



CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT 
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp

SELECT 
Value + SPACE(50 - LEN(Value)) + 'Description text'
FROM #tmp


Result:



----------------------------------------------------------------------------
L1 [ABC]                                  Description text
    L2 [DEFGH]                      Description text
        L3 [E]                      Description text

(3 row(s) affected)


----------------------------------------------------------------------------
L1 [ABC]                                          Description text
    L2 [DEFGH]                                    Description text
        L3 [E]                                    Description text