Inserting Strings Without Trailing Spaces SQL

Question

I have a database with a field named Field1 that has 100 nchars per entry. Each time I add a value, it is stored as:

"value     (100-ValueLength Spaces)        "

So Basically each stored value has a string of spaces after it. This is getting to be an issue when I try doing:

if (value == "Example")

because of all of the empty spaces after the string. How can I get it so the stored values don't have all of these trailing spaces?

Not sure, although I think it is plain SQL I'm using Microsoft SQL Server Management Studio 2008 if that helps at all. — sooprise
– sooprise, Commented May 13, 2010 at 17:36

Lukáš Lalinský · Accepted Answer · 2010-05-13 17:38:27Z

9

If you want a variable-length string, use nvarchar(100) instead of nchar(100). The later always has 100 characters, the former can have up to 100 characters, but doesn't fill up the space.

answered May 13, 2010 at 17:38

Lukáš Lalinský

41.5k6 gold badges109 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sooprise Over a year ago

Got it. When I started building my database, I didn't know the difference between nchar and nvarchar, and opted to use nchar, and now I realize the mistake I made.

Simon King Over a year ago

Useful answer. When might someone want to use nchar over nvarchar?

thelost · Accepted Answer · 2010-05-13 17:37:27Z

3

Use the sql LTRIM and RTRIM functions when inserting.

answered May 13, 2010 at 17:37

thelost

6,7044 gold badges31 silver badges44 bronze badges

2 Comments

sooprise Over a year ago

I'm using LINQ to insert, is there a LINQ equivalent to what you're talking about?

thelost Over a year ago

Then simply apply Trim to the string, there's no need to use sql functions in this case.

Justin Gregoire · Accepted Answer · 2010-05-13 17:40:07Z

2

Are you able to use a nvarchar, so that way there isnt padding added if you don't meet the required string length. If so that might be better then constantly having to trim your string entry.

answered May 13, 2010 at 17:40

Justin Gregoire

2,6541 gold badge20 silver badges9 bronze badges

1 Comment

sooprise Over a year ago

I just read up on the difference between nvarchar and nchar. I will try implementing this field type and see if that helps.

Collectives™ on Stack Overflow

Inserting Strings Without Trailing Spaces SQL

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related