SQL-Server: Creating a view with substring on a column, substring returns null when there is data

Question

I'm creating a view to get a substring of a column named ScopeContent in database X it has a datatype of ntext. When I'm looking at the value of ScopeContent in the view some of the data is null while others have data. For example, the first record has a datalength of 33000 but in the view that I created it shows as (NULL) and for the second record it has a datalength of 91578 and it shows the data in the view. Below is my sql statement.

If I understand correctly the way substring works is(column-name, starting position, length to)

CREATE VIEW ScopeContent2 (CatId, ScopeContent) 
 AS 
SELECT CatId, SUBSTRING(ScopeContent,32001,32000) AS ScopeContent 
FROM X 
  WHERE datalength(ScopeContent)>32000

For Example

CatId      ScopeContent
-----      ------------
    1      (NULL)
    2      rem ipsum dolor sit amet, consectetur adipiscing elit. Nam sed arcu posuere, pellentesque elit sit amet, ultricies mauris. Curabitur nec metus hendreri

`ntext` is a deprecated datytype in SQL Server, you better use `NVARCHAR(MAX)` instead. — dnoeth, Mar 11 '16 at 22:44

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Datalength counts bytes and not characters. In this case 2 bytes=1 character.

SUBSTRING takes character positions and not byte position.

In this light, your statement

For example, the first record has a datalength of 33000 but in the view that I created it shows as (NULL) and for the second record it has a datalength of 91578 and it shows the data in the view...

CREATE VIEW ScopeContent2 (CatId, ScopeContent) AS SELECT CatId, SUBSTRING(ScopeContent,32001,32000) AS ScopeContent FROM X WHERE datalength(ScopeContent)>32000

and query are inconsistent. As you try to extract using substring function data from 64002 bytes position (32001 characters) where as your data obviously is less than 64002 bytes(but greater than 32000 bytes as per your WHERE clause)

What you want probably is that you need sub string of query after 32000 characters if length of text is >32000

In which case your query should be

CREATE VIEW ScopeContent2 (CatId, ScopeContent) 
 AS 
 SELECT 
     CatId, 
     SUBSTRING(ScopeContent,32001,32000) AS ScopeContent 
 FROM X 
 WHERE datalength(ScopeContent)>64000 --- double of original value

mucio · Answer 2 · 2016-03-11T22:51:41.527

0

You can try:

CREATE VIEW ScopeContent2 (CatId, ScopeContent) 
         AS 
     SELECT CatId, SUBSTRING(ScopeContent,32001,32000) AS ScopeContent 
       FROM X 
      WHERE LEN(CAST(ScopeContent AS NVARCHAR(MAX))) > 32000

Or

CREATE VIEW ScopeContent2 (CatId, ScopeContent) 
         AS 
     SELECT CatId, SUBSTRING(ScopeContent,32001,32000) AS ScopeContent 
       FROM X 
      WHERE DataLength(ScopeContent) > 64000

The Len function returns the number of characters of a string, datalenght the number of bytes, which is twice the number of characters.

edited Mar 11 '16 at 22:51

answered Mar 11 '16 at 22:41

mucio

7,014
1
21
33

len() is an invalid argument for ntext data type – Andy N Mar 11 '16 at 22:46

score 0 · Answer 3 · answered Mar 11 '16 at 22:51

I guess mixing substring and datalength might be an issue. Datalength returns bytes (see https://msdn.microsoft.com/de-de/library/ms173486%28v=sql.120%29.aspx and http://www.sqlservercentral.com/Forums/Topic431183-8-1.aspx), while substring afaik works with chars. One character is not neccessarily one byte. Therefore you might run into troubles, as the datalength exceeds 32000 but the String is i.e. only 20000 chars (if 1 character is 2 bytes a 20000 chararacter string would be 40000 bytes).

I think dnoeth might be right in this, he was a bit faster :)

SQL-Server: Creating a view with substring on a column, substring returns null when there is data

3 Answers3