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
Variable string length - SQL
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Variable string length - SQL
Thanks,
Mark
Mark
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Variable string length - SQL
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Variable string length - SQL
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? TXnchar 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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Re: Variable string length - SQL
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
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
Mark
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Variable string length - SQL
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!
You can't see the view if you don't climb the mountain!
-
- NewLounger
- Posts: 5
- Joined: 02 Mar 2013, 16:23
Re: Variable string length - SQL
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
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