Variable string length - SQL

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Variable string length - SQL

Post by santosm »

Hi All,
I have used a nvarchar(20) field in my SQL table. I thought that using this method would make the field variable in size up to 20 characters. However, when I write a two character word into the field, it still displays at 20 characters in the form which is displaying these characters. I would really like it to only display what characters are typed into the field. What am I missing?

Thanks,
Mark
Thanks,
Mark

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Variable string length - SQL

Post by HansV »

I hope that someone who works with SQL Server (Wendell?) can answer this. The nvarchar data type should indeed be variable length, as opposed to nchar, which is fixed length.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Variable string length - SQL

Post by Rudi »

nchar and nvarchar can store Unicode characters.
char and varchar cannot store Unicode characters.
char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.
I'm not one to be able to answer your question...but just out of interest, what do you mean by "it still displays at 20 characters in the form". If you type two character in the text box, is it storing 18 spaces?? Please elaborate? TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

santosm
3StarLounger
Posts: 253
Joined: 19 Apr 2010, 09:01
Location: Indiana, USA

Re: Variable string length - SQL

Post by santosm »

Thanks for the reply Rudi and Hans,
Yes, if I write a two character word into the field, it stores 18 additional spaces for a total of 20. I will try varchar next to see what that does.

Thanks,
Mark
Thanks,
Mark

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Variable string length - SQL

Post by Wendell »

That's not a behavior I've seen before, but I very seldom use nvachar fields in SQL Server. The reason is mostly historical - in Access 2000 they caused all sorts of problems, while varchar worked just fine. The behavior you are seeing sounds like Access thinks it is actually an nchar field, but Access doesn't even have a field type that behaves that way. You might want to look at the ODBC driver you are using - that could be the culprit. The version that comes with Win7 or Win8x seems to be pretty solid from our experience, but some of the older drivers called "Native Client" have had some issues.
Wendell
You can't see the view if you don't climb the mountain!

bhjohnson100
NewLounger
Posts: 5
Joined: 02 Mar 2013, 16:23

Re: Variable string length - SQL

Post by bhjohnson100 »

While my experience with SQL Server isn't great, I've had to do quite a bit of read-only SQL.

I usually build a query in the Access grid, then test it out in SQL Manager (different syntaxes). Once it runs, I can copy/paste to Excel and check it out.

Many of the SQL Server fields defined as nchar/varchar, etc., give me trailing spaces. They are really easy to spot in Excel (just click on the right end of the formula line).

If there are leading/trailing spaces, I put in a LTRIM(RTrim(xxxx)) around the field names requested. Being lazy, I've written a macro to do all that stuff. This way, I never have to worry about exact matches or doing Trims in VBA.

HTH