I would like to get the character length of a column in SQL Server. Currently, I am using the sys.columns table. However, max_length is given in bytes. So if I have nvarchar(100), that column will show 200.

1. Is there a column someplace else that will give me the character length of 100 instead of the byte length of 200?

2. If not, am I safe to just do the math and divide the byte length by two for nvarchar columns? Or are there some complicating factors (cultures, code pages, etc, etc,...) that would make that not a smart thing to do?

I found a similar question, though the answer strikes me as incomplete since the OP asks about both varchar and nvarchar and the answer doesn't point out that the column is in bytes. Sure, the column will have 100 in the case of Column A varchar(100) as stated, but not in the case of Column B nvarchar(100): Querying Column Definition for Data Length - SQL Server.

Granted though, the question does specifically ask for data length in the title.

CodePudding user response：

There isn't a 100% safe way of doing this.

The max lengths are all ultimately defined in bytes (though for nvarchar by doubling the declared length) and the relationship between byte and character is not fixed.

• For varchar columns there has existed for a long time double byte collations where a character can consume 1 or 2 bytes.
• SQL Server 2012 added SC (Supplementary Character) collations that support UTF-16. In these collations a single nvarchar character may take 2 or 4 bytes.
• Even without those collations there is nothing stopping someone inserting values such as N'