0

I want MY_NAME column to have 4000 chars, I use MSSQL and Oracle. I Oracle it works as I can define

ALTER TABLE MY_TABLE MODIFY MY_NAME VARCHAR2(4000 char);

so it takes exactly 4000 chars, not bytes. If I would write in Oracle:

ALTER TABLE MY_TABLE MODIFY MY_NAME VARCHAR2(4000); -- (missing char word after 4000)

then it would mean 4000 bytes so it would never take 4000 special chars like ü for example because ü takse more than one byte.

For SQL Server I set its length to:

ALTER TABLE MY_TABLE ALTER COLUMN MY_NAME varchar(4000);

It works when I put there 4000 English chars but it doesn't work with special characters 4000 ć characters are too much for this column. How to setup this column to be independent from country / language and let it take maximum 4000 characters?

Thom A
  • 88,727
  • 11
  • 45
  • 75
michealAtmi
  • 1,012
  • 2
  • 14
  • 36
  • 2
    What do you mean "ć is too much"? Do you mean the character is being lost? If so, that's because the value is outside of the code page for the collation you are using. Likely you should therefore be using `nvarchar`. – Thom A Dec 02 '21 at 08:54
  • I don't want someone to put more than 4000 characters in this column. But I want 4000 characters even if they are special characters. A, b, c are normal characters, ćśąÄü are example of language - specific special characters. – michealAtmi Dec 02 '21 at 08:57
  • I don't see your point. a, b, c, ć, ś, ą, Ä, ü are all single characters... You aren't making sense here. – Thom A Dec 02 '21 at 08:59
  • they are two bytes characters, do u understand the difference between two commands for Oracle that I wrote ? do u know why Oracle is specyfying column length with char ang without char word ? – michealAtmi Dec 02 '21 at 09:00
  • 1
    In a `nvarchar` they are **all** 2 bytes characters... `nvarchar` uses UTF-16. `varchar` uses ANSI code pages, and are all stored as a 1 byte characters; if the character appears outside of the code page, the character is lost. Are you saying you want a UTF-8 collation? So `a` would be 1 byte, `ś` 2 bytes, and some emoticons 3 bytes? – Thom A Dec 02 '21 at 09:01
  • I just don't want to have more than 4000 characters, no matter if they are a, b, c or ć. I can currently put 4000 a characters, but when I put 4000 ć characters then exception is trhown. – michealAtmi Dec 02 '21 at 09:04
  • Yes... so why not use an [`nvarchar`/`nchar`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15)? You haven't answered that question. – Thom A Dec 02 '21 at 09:04
  • I will try with nvarchar :) I didn't know nvarchar :) thanks – michealAtmi Dec 02 '21 at 09:05
  • ... I told you about it [10 minutes ago](https://stackoverflow.com/questions/70196489/mssql-column-length-database-integrity-constraint-error-has-occured?noredirect=1#comment124088641_70196489)... – Thom A Dec 02 '21 at 09:05
  • I know but u then asked additional questions that I answered :) – michealAtmi Dec 02 '21 at 09:16
  • 1
    Does this answer your question? [What is the difference between varchar and nvarchar?](https://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) – Charlieface Dec 02 '21 at 09:38
  • @Larnu aside... On SQL Server `nvarchar` and associated string functions have UCS-2 semantics unless the current database is using an _SC (Supplementary Characters) collation - at which point all of the string functions get UTF-16 semantics. You can still store and retrieve UTF-16 data in a non-SC database but the string functions will give perhaps-unexpected results against it. – AlwaysLearning Dec 02 '21 at 09:40
  • One more question then. do I have to conver varchars to nvarchars? It seems to be problematic due to existing constrains on database. Could I instead change db encoding or something like that to let varchar take as many chars as I want independently whether those chars are special or not special characters ? – michealAtmi Dec 02 '21 at 14:38

0 Answers0