Are SQL strings null terminated?

I was learning how to use len() function. When I found out the length of a cell having 12 characters, it gave me result 12. Now I was thinking that arent the SQL strings null terminated(As if they would have been then len() should have returned 13 not 12)?

Please h开发者_开发问答elp me out.

Thanks

Well, first - the len function does not depend on null termination, programming languages not using null termination ALSO have a len function an it works.

Thus, a len function in SQL will give you the length of the string AS THE SERVER STORES IT - what do you care how that works?

Actually it will likely not be null terminated as this would make it hard to split a string over multiple database pages. And even if - this would be seriously implementation dependent (and you don't say which product you mean - the SQL language says nothing about how the server internally stores strings).

So, at the end your question is totally irrelevant. All that is relevant is that the len function implementation is compatible with the internal storage.

In SQL Server, LEN will ignore trailing spaces (http://msdn.microsoft.com/en-us/library/ms187403.aspx) - here's a modified example from that link:

PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON ;
GO

CREATE TABLE t1
    (
     charcol CHAR(16) NULL
    ,varcharcol VARCHAR(16) NULL
    ,varbinarycol VARBINARY(8)
    ) ;
GO
INSERT  INTO t1
VALUES  ('No blanks', 'No blanks', 0x00ee) ;
INSERT  INTO t1
VALUES  ('Trailing blank ', 'Trailing blank ', 0x00ee00) ;

SELECT  'CHAR' = '>' + charcol + '<'
       ,'VARCHAR' = '>' + varcharcol + '<'
       ,varbinarycol
       ,LEN(charcol)
       ,LEN(varcharcol)
       ,DATALENGTH(charcol)
       ,DATALENGTH(varcharcol)
FROM    t1 ;
GO

PRINT 'Testing with ANSI_PADDING OFF' ;
SET ANSI_PADDING OFF ;
GO

CREATE TABLE t2
    (
     charcol CHAR(16) NULL
    ,varcharcol VARCHAR(16) NULL
    ,varbinarycol VARBINARY(8)
    ) ;
GO
INSERT  INTO t2
VALUES  ('No blanks', 'No blanks', 0x00ee) ;
INSERT  INTO t2
VALUES  ('Trailing blank ', 'Trailing blank ', 0x00ee00) ;

SELECT  'CHAR' = '>' + charcol + '<'
       ,'VARCHAR' = '>' + varcharcol + '<'
       ,varbinarycol
       ,LEN(charcol)
       ,LEN(varcharcol)
       ,DATALENGTH(charcol)
       ,DATALENGTH(varcharcol)
FROM    t2 ;
GO

DROP TABLE t1
DROP TABLE t2

If we're talking pure SQL, there's no NUL terminator for you to worry about. If we're talking interfacing to SQL from other languages (e.g. C), then the answer depends on the language in question.

There are a couple of relevant points worth remembering:

1. There are two character types in SQL: CHAR(N) and VARCHAR(N). The former is always the same length (N) and padded with spaces; the latter is variable-length (up to N chars).

2. In Transact-SQL, LEN returns the length on the string excluding trailing spaces.

In some SQL strings are zero- terminated. On some SQL they have a leading length byte/ word.

This of course does not matter for the len() function.
But it does matter if you want to insert a \0 into the string.

Usually varchar has a leading length byte. But char is \0 terminated.