Preserving the total field lenght
-
Hello comunity
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
B001 Bank 123
How can accomplish this query.
Many thanks
Luis Santos
-
Luis,
Do you mean that you only want actual blanks because the char inserts to fill.
Do you wish to trim the namebank column?
create table x (b1 char(10), b2 char(15))insert into x (b1, b2) values
('h', 'w'),
('he ', 'wo ')
select b1, b2, b1 + b2
from x
-
Hello
I explain better why i need to preserve whitespaces.
I have one field that keep my bank C(10) + codbank C(15), like this:
Select bank + codbank from tblbanks
B010 Bank 123
Then if my bank is:
B1 i need to keep like this:
B1 Bank 456
Ok, i can use case statement to check LEN(bank), but what i want is avoid the Case statment.
I hope i could explain better the situation,
Best regards
Luis Santos
-
luissantos (6/24/2014)
HelloI explain better why i need to preserve whitespaces.
I have one field that keep my bank C(10) + codbank C(15), like this:
Select bank + codbank from tblbanks
B010 Bank 123
Then if my bank is:
B1 i need to keep like this:
B1 Bank 456
Ok, i can use case statement to check LEN(bank), but what i want is avoid the Case statment.
I hope i could explain better the situation,
Best regards
Luis Santos
Your explanation does not make any sense to me. Why do you need LEN here? You know exactly how long each row is because they are defined as char. And no clue what a CASE expression has to do with it. One challenge here is that HTML does not keep the formatting. Instead post your data in a temp table or some other type of consumable format.
Look at the following example. There is no need to check the length or use a case expression to maintain the spaces.
create table #something
(
Bank char(10),
CODBank char(15)
)
insert #something
select 'B1', 'Bank 456'
select Bank + CODBank
from #something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hello Sean
Thanks for your reply, also i solve like this:
DECLARE @meubanco CHAR(10)
SET @meubanco = '1'
SELECT
cast(replace(@meubanco,'',' ')as char(10)) + conta
FROM bl
Maybe, that´s make no sense, but on the application that i use (not my application), like i said i have a field named OLLOCAL that concate bank+codbank, but they don´t keep or respect the blank spaces when the INSERT or UPDATE Trigger fired, that´s why i need to do this.
Best regards,
Luis Santos
-
A variable or a column with data type char(10) should give you 10 spaces. If it's not, then at some point in your code or in the trigger it might be being assigned to a variable or a column of type varchar() and that will trim the trailing spaces. Check the assignments.
And then again, I might be wrong ...
David Webb -
luissantos (6/24/2014)
Hello SeanThanks for your reply, also i solve like this:
DECLARE @meubanco CHAR(10)
SET @meubanco = '1'
SELECT
cast(replace(@meubanco,'',' ')as char(10)) + conta
FROM bl
Maybe, that´s make no sense, but on the application that i use (not my application), like i said i have a field named OLLOCAL that concate bank+codbank, but they don´t keep or respect the blank spaces when the INSERT or UPDATE Trigger fired, that´s why i need to do this.
Best regards,
Luis Santos
The replace in that query makes no sense. It doesn't find an empty string and replace it with a space.
This is 100% the same thing.
DECLARE @meubanco CHAR(10)
SET @meubanco = '1'
select @meubanco + conta
from bl
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
i think your problem is presentation not data
in a non-monospace font the "space" uses less pixels in width than a lettertry this:
create table #something
(
Bank char(10),
CODBank char(15)
)
insert #something
values( 'B1', 'Bank 456'), ('B123','Bank 456')select Bank + CODBank as Wrong, REPLACE(BANK, ' ','_') + CODBank AS Correct
from #somethingso where do you want to present the data?
if you use a monospace font you get all chars with same widthopen this simple sample in a browser
<html>
<header>
</header>
<body>
<table border="1" cellpadding="0" cellspacing"0" >
<tr>
<td><span>html ignores more than one space</span></td>
<td><span>spaces use less width</span></td>
<td><span>using monospace font</span></td>
</tr>
<tr>
<td><span>B1 Bank 456</span></td>
<td><span>B1 Bank 456</span></td>
<td><span style="font-family: 'Courier New';">B1 Bank 456</span></td>
</tr>
<tr>
<td><span>B123 Bank 456</span></td>
<td><span>B123 Bank 456</span></td>
<td><span style="font-family: 'Courier New';">B123 Bank 456</span></td>
</tr>
</table>
</body>
</html> -
i just realize this post has 4 years ...
- paulo.margarido - Thursday, May 17, 2018 5:34 AM
It's ok. Always good to have more info because even if the OP can't use some info after 4 years, it might help someone else that stumbles across the post when they're looking for an answer for the same question.
--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) -
Just for curiosity i find out that in non-monospace font the "space" has something about half the width of other characters
so we can have:
declare @tabela table (id int identity(1,1), value decimal(10,2))
insert into @tabela
values(1.0),(123.54),(52.0),(12),(1568),(425.36),(1.0),(0.25),(558),(12),(10000),(9587.25)
select *
, REPLICATE(' ',(10-LEN(value))*2) + CAST(value as varchar(10)) as align_value
, '€' + REPLICATE(' ',(10-LEN(value))*2) + CAST(value as varchar(10)) as align_value2
from @tabela
that looks bad in plain text:
id value align_value align_value2
1 1.00 1.00 € 1.00
2 123.54 123.54 € 123.54
3 52.00 52.00 € 52.00
4 12.00 12.00 € 12.00
5 1568.00 1568.00 € 1568.00
6 425.36 425.36 € 425.36
7 1.00 1.00 € 1.00
8 0.25 0.25 € 0.25
9 558.00 558.00 € 558.00
10 12.00 12.00 € 12.00
11 10000.00 10000.00 € 10000.00
12 9587.25 9587.25 € 9587.25
but showing results has table ...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply