Merkwürdiges Verhalten von Substring() ?

Question

Hallo,

ich bin auf ein merkwürdiges Verhalten von Substring() gestoßen, weshalb ich hier einmal nachfragen möchte. Ich möchte eine Zeichenkette in einer Spalte vom Typ SQL_VARIANT speichern (jaja, ich weiß, schlechtes Design usw. bla bla aber darum geht es hier nicht!), wobei die Länge der Zeichenkette variiert. Merkwürdig hierbei ist, dass Substring() unterschiedliche Ergebnisse liefert, je nachdem ob ich eine Konstante oder Variable als dritten Parameter übergebe - zwar nicht, was den zurückgeliefertern Wert anbelangt, aber die Variant-Properties:

Ausgangssituation:

Declare @v sql_variant
Declare @alphabet nvarchar(max) = 'abcdefghijklmnopqrstuvwxyz'
Declare @len int = 10

Mit Konstante:

Set @v = SUBSTRING(cast(@alphabet as ntext), 1, 10)
Select @v, SQL_VARIANT_PROPERTY (@v, 'MaxLength')

Wert    MaxLength
---------- ---------
abcdefghij 20

Mit Variable:

Set @v = SUBSTRING(cast(@alphabet as ntext), 1, @len)
Select @v, SQL_VARIANT_PROPERTY(@v, 'MaxLength')

Wert    MaxLength
---------- ---------
abcdefghij 8000

Dies verstehe ich nicht! Hat jemand eine Erklärung dafür? Oder besser gar eine Idee, wie ich ersteres auch mit Variablen hinbekomme? Der Weg über dynamisches Sql scheidet leider aus, da ich dies innerhalb einer benutzerdefinierten Funktion benötige...

Answer 1

Mit Konstante:

 

SQL_VARIANT_PROPERTY(@v, 'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@v, 'MaxLength') MaxLength

TotalBytes = 28; MaxLength = 2*Stringlänge

Mit Variable:

 

SQL_VARIANT_PROPERTY(@v, 'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@v, 'MaxLength') MaxLength

TotalBytes = 2*StringLänge; MaxLength = 8000

Wenn man nicht @v nicht explizit castet dann kann man nicht sicher sein in welchen Typ letztlich die Variable vom System gecastet wird.
In Fall der Konstante in nvarchar, im Variablenfall (wahrscheinlich) in varbinary(8000)

Wenn im Variablenfall explizit nach nvarchar konvertiert wird sind die Ergebnisse identisch:

set

 

@v = convert(nvarchar, SUBSTRING(cast(@alphabet as ntext), 1, @len))
select
   @v,
   SQL_VARIANT_PROPERTY(@v, 'TotalBytes') TotalBytes,
   SQL_VARIANT_PROPERTY(@v, 'MaxLength') Wert

Wert TotalBytes MaxLength
------------------------------
abcdefghij 28 60

 

 

 

Answer 2

Hallo Praktikant,

danke für deine Antwort. Leider ist für mich (manche behaupten, ich sei ein Erbsenzähler) 60 NICHT gleich 20, so dass von "identischen Ergebnissen" leider keine Rede sein kann ;-)

Du Convert()est den Rückgabewert von Substring() in ein nvarchar ohne Größenangabe - und die sind dann per default 30 Zeichen lang, daher der Wert von 60 in MaxLength.

Leider nicht die Lösung...

Answer 3

Hallo Yury,

auch an dich ein Dankeschön für die Antwort. Allerdings stimme ich da nicht mit dir überein, da ja in beiden Fällen das Substring() von ntext nvarchar zurückliefert - demnach müsste ja bei beiden Varianten 8000 als MaxLength angezeigt werden...

Answer 4

Set @v = SUBSTRING(cast(@alphabet as ntext), 1, @len)
Select @v, DATALENGTH (cast(@v as nvarchar(max)))

---

Gruß Yury

Answer 5

Hallo Jury,

nochmal danke für die Antwort. Leider ist das noch immer nicht ganz die Lösung; es funktioniert zwar für diesen Fall, aber wenn man statt einer Länge von 10 eine von 30 verwendet weicht das Ergebnis von Datalength auch ab.

Interessant ist, dass Substring(@Alphabet, 1, 30) eine MaxLength von 60 zurückliefert, obwohl @Alphabet nur 26 Zeichen hat (man also eine Length von 52 erwarten könnte). Genau dies ist aber der für mich interessante Aspekt, da ich an andere Stelle diese Länge auslesen und weiterverwenden möchte...

Die ursprüngliche Frage war jedoch (zumindest gemeint, wenn auch vielleicht nicht ausdrücklich so formuliert ;-): Warum liefert Substring() unterschiedliche Ergebnisse, wenn man als dritten Parameter eine Konstante oder Variable (mit an sich gleichen Werten) übergibt?

Answer 6

Hi,

ist es möglich, nchar(4000) statt nvarchar(max) einzusetzen?

Declare @v sql_variant
Declare @alphabet1 nvarchar(max) = 'abcdefghijklmnopqrstuvwxyz'
Declare @len int = 30

declare @alphabet nchar(4000) = @alphabet1

Set @v = SUBSTRING(cast(@alphabet as ntext), 1, 30)
Select @v, SQL_VARIANT_PROPERTY (@v, 'MaxLength')

Set @v = SUBSTRING(cast(@alphabet as ntext), 1, @len )
Select @v, DATALENGTH (cast(@v as nvarchar(max)))

---

Gruß Yury

Answer 7

hi,

Interessant ist, dass Substring(@Alphabet, 1, 30) eine MaxLength von 60 zurückliefert, obwohl @Alphabet nur 26 Zeichen hat (man also eine Length von 52 erwarten könnte). Genau dies ist aber der für mich interessante Aspekt, da ich an andere Stelle diese Länge auslesen und weiterverwenden möchte...

Das ist das einfache an deinem Fall:

Alle Unicode-Datentypen belegen den doppelten Speicher, siehe:
   http://msdn.microsoft.com/de-de/library/ms186939.aspx

Das interessantere Problem ist, das bei der Verwendung von SUBSTRING() mit Konstante wohl ein NVARCHAR(10) ensteht, dagegen bei der verwendung der Variablen ein NVARCHAR(max).

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 8

hi,

hier die Antwort von Erland Sommarskog, SQL Server MVP:

MaxLength reflects the the data type of the expression. T-SQL is a
statically typed language, so when the length is expressed with
a variable, the type of the expression is the same as the type of
the first argument. When you have a constant, that controls the type
(if the constant is < the max length of the base column, that is).

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 9

Hallo,

als Ergänzung zu Stefans (Erlands) Antwort:

MaxLength gibt wie auch Erland betont die deklarierte Länge an,
wie man sie bei DECLARE @V NVARCHAR(n ) mit n vorgibt.

Der SQL Server wählt bei der Verwendung Variablen die defensivste Einstellung,
für eine Funktion, um bei der Verwendung in Abfrageausdrücken bei wechselnden Werten
(oben für @len ) einen konstanten Abfrageplan zu erhalten, wie beschrieben in
Zwischenspeichern und Wiederverwenden von Ausführungsplänen

Für Zeichendaten wäre das hier N[VAR]CHAR(4000) für Unicode bzw. [VAR]CHAR(8000) für Ansi.
Eine zusammenfassende Beschreibung für das Verhalten findet sich u. a.
unter Erzwungene Parametrisierung im Abschnitt  Parameterdatentypen.

Willst Du die effektive Länge der Zeichenkette in einem SQL_VARIANT erhalten,
kannst Du dafür DATALENGTH verwenden, wobei der Wert für Unicode durch 2 geteilt
werden muß, um die Anzahl der Zeichen zu erhalten, beschrieben unter
Verwenden von sql_variant-Daten

Im übrigen solltest Du auf den CAST auf NTEXT verzichten bei NVARCHAR(MAX),
was einem impliziten CAST auf NVARCHAR(4000) auf Umwegen entspricht und
anstatt dessen direkt verwenden:

SET @v = SUBSTRING(CAST(@alphabet AS nvarchar(4000)), 1, @len)
SELECT @v AS Value, DATALENGTH(@v) AS DataLength, DATALENGTH(@v) / 2 AS CharacterLength

Gruß Elmar

Answer 10

Hallo Elmar,

danke, das erklärt es für mich hinreichend!

MaxLength gibt wie auch Erland betont die deklarierte Länge an,
wie man sie bei DECLARE @V NVARCHAR(n ) mit n vorgibt.

Was ich nun eigentlich suche, ist die Möglichkeit, diese "deklarierte Länge" (dieser Begriff gefällt mir gut btw) für eine Zeichenkette vorzugeben, die in einem SQL_VARIANT gespeichert ist. Also ich möchte wissen, dass eine Zeichenkette sagen wir mal 30 Zeichen lang sein kann, auch wenn sie aktuell evtl. kürzer ist. (Warum ich das möchte, ist relativ aufwändig zu erklären, ich spare es mir hier einfach mal...)

Geht so etwas?

Answer 11

Hallo,

nein, so etwas geht an der Stelle nicht.

Willst Du sicherstellen, dass eine Länge nicht überschritten wird,
wirst Du bei sql_variant die Länge manuell anpassen müssen -
via SUBSTRING, LEFT etc.

Willst Du eine Minimallänge nicht unterschreiten, könntest Du [N]CHAR verwenden,
damit würde die Zeichenkette mit Leerstellen "aufgepolstert".
Oder mit REPLICATE arbeiten (bei Unicode kein SPACE verwenden).

Gruß Elmar