Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009

    Unanswered: What are the 2 byte characters?

    I want to see which characters are stored as 2 bytes in a sybase database.

    I googled it but couldn't find any results?

    Also what does varchar(255) mean?

    Does it mean "maximum 255 bytes" or "maximum 254 bytes"???

    Thanks from now

  2. #2
    Join Date
    Jun 2007
    Quote Originally Posted by xotesa
    Also what does varchar(255) mean?

    Does it mean "maximum 255 bytes" or "maximum 254 bytes"???
    Why not spend 30 seconds and work out the answer to this yourself?
    declare @txt      varchar(255)
    select  @txt = "012345678901234567890123456789 ..."
    select  @txt
    Is the answer the same if you use char(255)?

  3. #3
    Join Date
    Jun 2009
    South Africa
    Gotta love Mike's reply

    Try the following and look at the results :
    use tempdb
    create table #abc ( a int , vc varchar(10), uvc univarchar(10), ch char(10), uch unichar(10) )
    insert into #abc values ( 1, 'abc', 'abc', 'abc', 'abc')
    insert into #abc values ( 2, 'abc  ', 'abc  ', 'abc  ', 'abc  ')
    insert into #abc values ( 3, 'abcdefghijkl', 'abcdefghijkl', 'abcdefghijkl', 'abcdefghijkl')
    select a,vc,uvc,ch,uch 
    from #abc
    select a, 'chlen_vc'=char_length(vc), 'dtlen_vc'=datalength(vc), 'chlen_uvc'=char_length(uvc), 'dtlen_uvc'=datalength(uvc), 'chlen_ch'=char_length(ch), 'dtlen_ch'=datalength(ch), 'chlen_uch'=char_length(uch), 'dtlen_uch'=datalength(uch)
    from #abc
    -- exec sp_help #abc
    -- go
    select c.colid, c.type, c.length, c.usertype, 'colname'=convert(varchar(10),, 'datatype'=convert(varchar(10), 
    from syscolumns c, systypes t 
    where id = object_id('#abc')
    and c.usertype = t.usertype
    drop table #abc
    Results here :
    a           vc         uvc        ch         uch        
    ----------- --         ---        --         ---        
              1 abc        abc        abc        abc        
              2 abc        abc        abc        abc        
              3 abcdefghij abcdefghij abcdefghij abcdefghij 
    a           chlen_vc    dtlen_vc    chlen_uvc   dtlen_uvc   chlen_ch    dtlen_ch    chlen_uch   dtlen_uch   
    ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
              1           3           3           3           6          10          10          10          20 
              2           3           3           3           6          10          10          10          20 
              3          10          10          10          20          10          10          10          20 
    colid       type        length      usertype    colname    datatype   
    ----------- ----------- ----------- ----------- -------    --------   
              1          56           4           7 a          int        
              2          39          10           2 vc         varchar    
              3         155          20          35 uvc        univarchar 
              4          47          10           1 ch         char       
              5         135          20          34 uch        unichar

    The vc is a varchar() column, the uvc is a univarchar() column. Both of these are "variable length" columns where the portion inside the parenthesis indicate the maximum character length. ( So varchar(255) indicates a max of 255 characters that can be contained. )
    The same goes for the maximum character length of char() and unichar() columns, but they are deemed fixed-length datatypes.

    For the *varchar datatypes, blanks or spaces are trimmed off the trailing end, and is also stored without the trailing spaces inside the database.
    For the *char datatype, blanks are padded onto the end if the string is less than the actual maximum string size, and it's also stored in the database with the padded spaces on the end.

    The unichar and univarchar types are stored as "double-byte" characters, as is indicated by the datalength() function.

    If you look at the results, you'll see that char_length returns the actual length of the string that was stored. For varchar and univarchar, this means that 'abc' and 'abc ' is both stored as 'abc' in this case.
    If you look at the datalength() of the two types, you will see that the varchar() uses half the storage of the univarchar(), so the univarchar is stored as double-byte characters.

    The interesting thing to note is the char() & unichar() vs the varchar() & univarchar() - their character length and data length always indicate the padded spaces as well : character length and data length of 10 for char(), and character length of 10 and data length of 20 (as per the example).

    Note that for row 3, the 'k' & 'l' characters were trimmed from 'abcdefghijkl' as the maximim column length for all columns was defined as 10.

  4. #4
    Join Date
    Jun 2009

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts