Does any option exist to generally let the database interpret the length of varchar columns as number of characters instead of number of bytes?

E.g. when loading an existing table definition into an UTF-8 database the columns might be too small for the data. A workaround is to re-define the varchar columns with an explicit character option, but a general option would be more convenient.

example:

old db: varchar(5)
new utf-8 db requires: varchar(5 character)

asked 08 Jan '14, 10:48

Martin's gravatar image

Martin
8.6k115149237
accept rate: 14%

edited 11 Jan '14, 07:59

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371108

2

Turn this into a "product suggestion" for something like SET OPTION PUBLIC.default_varchar_length_semantics = 'byte' (default) or 'character'... I'll support it even though I don't need it. I can see the need.

(08 Jan '14, 14:28) Breck Carter
Replies hidden

What would be the official way to suggest such an enhancement?

(09 Jan '14, 06:54) Martin

"the official way to suggest"

Don't know. However, AFAIK, iAnywhere/Sybase/SAP staff members have promised to take notice of questions in this forum tagged as "product-suggestion" - probably particularly those with up-votes.

(09 Jan '14, 07:26) Volker Barth

You could switch to using the nvarchar datatype in your new database. The length specified for nvarchar is characters, not bytes.
dcx link

permanent link

answered 09 Jan '14, 10:03

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

...however, that would also require to change the table definition, and that is not desired here, according to Martin.

BTW: Would that not possibly have further implications (a different default collation at least)?

(09 Jan '14, 10:31) Volker Barth

Yes, the table def would have to change in the new UTF8 database - but it has to change regardless because the current schema is not working. There may be collation issues to deal with, but there already are if he is moving from a non-UTF8 database to a UTF8 database, so it is not really any extra work to deal with it.

(09 Jan '14, 11:13) Jason Hinspe...
Replies hidden
1

Apparently, that situation could be handled rather easy when one uses user-defined domains, i.e. has defined something like DT_MYCHAR_50 which could be re-defined in the new database from CHAR(50) to CHAR(50 CHAR), so instead of altering many tables/view/STPs one would only have to adapt a few domains - but yes, that's a post-mortem hint:)

(09 Jan '14, 12:35) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×10
×8

question asked: 08 Jan '14, 10:48

question was seen: 1,010 times

last updated: 11 Jan '14, 07:59