treatment of white space in string operations

Question

Post reply

treatment of white space in string operations

Dimitrije Mišic

Say Hey Kid

Points: 695
More actions
August 3, 2009 at 8:23 am

#137552

select SUBSTRING( '1234567' + ' ',0,12) + '89' union all select SUBSTRING('123' + ' ',0,12) + '456789'
does not return 4 below 8 but moves it to the left, which is inconvenient, for I'm tinkering with the Ajax toolkits auto-complete extender. Trying to make it multi-column without doing too much work. And it does work... sort of but I would like to make second column start at the fixed length so as not to be ugly.
Is there any way I can achieve this effect?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

myheadhurts Ten Centuries Points: 1262 More actions · Answer 1

I think your prob is not in the SQL.

In MSMS, if you send your Results to Text, you get this:

1234567 89

123 456789

1 23456789

Isn't this what you are trying to acheive?

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

You might also consider using the char datatype, which is fixed length, instead of padding and using SUBSTRING. It will save you a few CPU cycles.

declare @char char(10)

set @char = '12345'

select @char+'67890'

-- or

select cast('12345' as char(10))+'67890'

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

I guess I don't understand... textual datatypes are naturally "left justified" and there is no need to RPAD.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dimitrije Mišic Say Hey Kid Points: 695 More actions · Answer 4

Yeah, you are right sql is not the problem. Apparently problem is the treatment of white-space in .NET display controls. For when you run this query and send results to grid you get them garbled, and apparently same thing happens when I send this result as datatable to my c#-asp.net code.

which means this is not the right forum for this question.

Anyway, I am grateful for your replies, thank you.