May 15, 2009 at 1:08 pm
I have a table that has a field that is defined as VarChar(300). My insert stored procedure has a parameter called @Description that is a VarChar(300). I need to be able to preserve the trailing spaces in this field. In other words if the program that calls the insert stored procedure sets the variable @Description so that it has x number of trailing blanks, I need to preserve those trailing blanks. Is there anyway to do this if I define @Description as a VarChar or do I need to make it a Char?
Thanks
ps. I know that trailing blanks are a waste. I am doing this because of a bizarre requirement that is too complicated to explain.
May 15, 2009 at 1:19 pm
Vrachar type removes trailing spaces... you must use the CHAR type
May 15, 2009 at 1:34 pm
Thanks. I had a feeling that I would have to change the datatype from VarChar to Char. I was hoping that there was something else that I could have done.
Thanks again.
May 15, 2009 at 1:44 pm
I am not sure CHAR will do what you are looking for either. Take the following example.
Declare @test-2 char(50)
Set @test-2 = 'a '
Select LEN(@test + '|')
The length is 51, not 4. In your case everything will have trailing spaces of up to 300 characters instead of the number of empty characters submitted by the application.
May 15, 2009 at 1:52 pm
I'm curious why you need to store this trailing spaces. 🙂
May 15, 2009 at 1:54 pm
Take this code for example. It uses the Varchar datatype, and if you concatenate another character to the end, you can see that it actually did perserve the trailing spaces.
Declare @Table Table (test VARCHAR(50))
Insert into @Table VALUES ('a ')
Select LEN(test + '|'), test + '|'
FROM @Table
May 15, 2009 at 2:14 pm
The reason behind this is hard to explain. Let me try.
I am interfacing to a table in a legacy system that has 10 description fields defined as Desc_1, Desc_2, Desc_3, Desc4... Desc_10. My boss wants me to create my table in the same manner. I do not want to put 10 repeating fields in my table. Therefore I have created one 300 byte field called Description. My goal is to be able to map my one 300 byte field into 10 fields of 30 bytes so that I so that I can interface with the legacy system.
Hopefully this clarifies the reason behind my post.
Thanks
May 15, 2009 at 2:19 pm
Sounds like a normalization problem. What about a description table with a foreign key and a sequence instead of concatenated multi-value column? You will run into huge performance issues if you have to search for specific descriptions by using LIKE or SUBSTRING.
May 15, 2009 at 2:24 pm
I was thinking the same thing as Florian.
Something like...
MainTable
MainTableID
OtherFieilds
MainTableDscr
MainTableID
DscrID
DscrTable
DscrID
DscrSequence
Dscr
May 15, 2009 at 2:37 pm
If all you wanna do is to have a 300 char field containing all the 10 description fields from the legacy table... you just have to do this:
Insert last_table set description300= description1+replicate(' ',30-len(description1)) +description2+replicate(' ',30-len(description2)) +.....+description10 from legacy_table
However... your scenario is kinda strange .... you should take a deeper look on your design...
May 15, 2009 at 3:15 pm
FelixG (5/15/2009)
Vrachar type removes trailing spaces... you must use the CHAR type
It doesn't seem to be doing that on my system:
--========
declare @s-2 varchar(30)
declare @t varchar(30)
set @s-2 = 'K '
Select @t = @s-2
select datalength(@s), datalength(@t)
Declare @D as table (st varchar(30))
INSERT into @D Select @s-2
INSERT into @D Select @t
SELECT st, datalength(st) From @D
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 3:25 pm
FelixG (5/15/2009)
Vrachar type removes trailing spaces... you must use the CHAR type
For what it is worth, SQL doesn't remove trailing spaces when inserting into a VARCHAR. Rather, SQL ignores them when making comparisons.
EDIT: Bah.. I had to go to a meeting.. Barry bet me 🙂
May 15, 2009 at 3:38 pm
RBarryYoung (5/15/2009)
FelixG (5/15/2009)
Vrachar type removes trailing spaces... you must use the CHAR typeIt doesn't seem to be doing that on my system:
--========
declare @s-2 varchar(30)
declare @t varchar(30)
set @s-2 = 'K '
Select @t = @s-2
select datalength(@s), datalength(@t)
Declare @D as table (st varchar(30))
INSERT into @D Select @s-2
INSERT into @D Select @t
SELECT st, datalength(st) From @D
:pinch:
Thanks Barry for showing me the wood between the trees!!
I still think there is a normalization problem.
May 15, 2009 at 6:42 pm
Florian Reischl (5/15/2009)
RBarryYoung (5/15/2009)
FelixG (5/15/2009)
Vrachar type removes trailing spaces... you must use the CHAR typeIt doesn't seem to be doing that on my system:
--========
declare @s-2 varchar(30)
declare @t varchar(30)
set @s-2 = 'K '
Select @t = @s-2
select datalength(@s), datalength(@t)
Declare @D as table (st varchar(30))
INSERT into @D Select @s-2
INSERT into @D Select @t
SELECT st, datalength(st) From @D
:pinch:
Thanks Barry for showing me the wood between the trees!!
I still think there is a normalization problem.
Agreed.
As to the "Varchars trim spaces" belief, I suspect that it got it's start from the fact that the LEN(..) function in fact, does not count trailing spaces (that's why I had to use the DATALENGTH() function).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 6:46 pm
Florian Reischl (5/15/2009)
Sounds like a normalization problem. What about a description table with a foreign key and a sequence instead of concatenated multi-value column? You will run into huge performance issues if you have to search for specific descriptions by using LIKE or SUBSTRING.
I proposed that, but I was told that I had to use one table.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply