Why CAST to a Short VARCHAR Returns a Star Instead of an Error

You expect either 634 or an error, and SQL Server gives you a star. A short VARCHAR conversion can replace an integer that does not fit, without preserving any of its digits.

Reproduce the Short VARCHAR Star

CAST(634 AS varchar(2)) returns a single asterisk. It does not return the first two digits. The integer needs three character positions, but the requested character target provides two. SQL Server uses the star as the replacement for this insufficient-width integer conversion.

The same issue affects a short char target, with fixed-length padding around its stored representation. This behavior belongs to the integer-to-character conversion. Do not extend it into a rule saying every insufficient conversion returns a star. Other source and destination types follow different rules.

Run the next query in SSMS. Inspect the values and lengths rather than relying only on a grid cell's appearance. An asterisk can look like intentional masking when it is actually lost information. It is a very small symbol for a fairly large problem.

SELECT CAST(634 AS varchar(2)) AS ShortIntegerText,
    CAST(634 AS varchar(3)) AS FittingIntegerText,
    CAST(634 AS char(2)) AS FixedWidthText,
    LEN(CAST(634 AS varchar(2))) AS VisibleLength,
    DATALENGTH(CAST(634 AS char(2))) AS FixedWidthBytes;

An Error-Free Statement Can Lose Meaning

The expression succeeds, so TRY CATCH does not raise an alarm. A reporting expression, export, or computed value can carry the star into another system. A successful insert also says little if its source expression already discarded the integer.

I inspect conversion lengths when an identifier column unexpectedly contains stars. The first question is where the numeric value became text. Checking only the destination column's generous width misses a narrow CAST earlier in the expression. Widening the final destination does not restore the discarded digits.

Find the earliest narrow conversion in the write path. Check stored procedures, view expressions, import statements, and application-generated SQL. Preserve the original numeric value during diagnosis. Without it, a star tells you that something was lost, but it cannot tell you which integer belonged there.

NVARCHAR Takes a Different Failure Path

The corresponding CAST to nvarchar(2) raises an arithmetic-overflow error for this integer. Use a caught demonstration so the rest of your query window remains usable. The point is the type difference, not a recommendation to select Unicode simply for its error behavior.

TRY_CONVERT to that nvarchar target returns NULL for this conversion failure. TRY_CONVERT to the short varchar target still returns a star, because that conversion succeeded according to its rules. A null-check around TRY_CONVERT therefore does not catch every lossy conversion.

BEGIN TRY
    SELECT CAST(634 AS nvarchar(2)) AS TooShortUnicodeText;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT TRY_CONVERT(nvarchar(2), 634) AS UnicodeTryResult,
    TRY_CONVERT(varchar(2), 634) AS CharacterTryResult;

String Truncation to a Short VARCHAR Is a Separate Rule

Start with the text value '634' instead of the integer 634. An explicit conversion to varchar(2) truncates that string to its leading characters. The expression has the same target width but a different source type. That difference explains why apparently similar test queries disagree.

Text converted to a short nvarchar target also truncates in this explicit-cast example. Do not confuse expression conversion with inserting oversized text into a table column. Inserts and updates under normal warning settings can raise truncation errors. Variable assignments and explicit casts have their own behavior.

Which source type reaches the conversion in your real query? Inspect that expression, not just the value displayed in the output. A parameter typed as int and a parameter typed as varchar can produce different failure behavior even when both appear to contain 634.

SELECT CAST('634' AS varchar(2)) AS ShortStringText,
    CAST(N'634' AS nvarchar(2)) AS ShortUnicodeStringText,
    CAST(634 AS varchar(2)) AS ShortIntegerText;
DECLARE @AssignedText varchar(2);
SET @AssignedText = '634';
SELECT @AssignedText AS VariableAssignmentText;

Find Short VARCHAR Stars in Stored Data Without Guessing

Create a temporary audit example that retains both the raw integer and converted text. The deliberately narrow conversion illustrates how a wider destination can store a star without complaint. Use a source key to keep every result traceable to its original input.

Search for the exact placeholder rather than every string containing a star. If the business permits literal asterisks, classify them separately. For fixed char values, trailing padding also affects byte length. TRIM helps inspect the visible placeholder, but it does not establish its cause.

CREATE TABLE #ConversionAudit
(
    SourceID int NOT NULL PRIMARY KEY,
    SourceNumber int NOT NULL,
    StoredText varchar(30) NOT NULL
);
INSERT #ConversionAudit
SELECT SourceID, SourceNumber, CONVERT(varchar(2), SourceNumber)
FROM (VALUES (1, 63), (2, 634), (3, -12)) AS s(SourceID, SourceNumber);
SELECT SourceID, SourceNumber, StoredText,
    CONVERT(varchar(11), SourceNumber) AS RebuiltText
FROM #ConversionAudit
WHERE TRIM(StoredText) = '*';

Choose Width From the Type's Full Range

An int needs up to eleven characters when its negative sign is included. A bigint needs up to twenty. Size the conversion for the supported type range, then validate any narrower business rule independently. Choosing width from today's largest positive value makes tomorrow's boundary a surprise.

Numeric identifiers belong in numeric columns when the application treats them as numbers. Text identifiers with leading zeros have a different contract. Converting an integer cannot recover leading zeros that never existed in its numeric representation. Decide which representation is authoritative before formatting it.

I include negative boundaries in conversion tests, even when current data is positive. A later adjustment can introduce a sign. The minimum int also needs careful literal typing, so the next query explicitly converts the boundary literal to int before testing its text representation.

DECLARE @LowestInt int = CONVERT(int, '-2147483648');
DECLARE @LowestBigint bigint = CONVERT(bigint, '-9223372036854775808');
SELECT CONVERT(varchar(11), @LowestInt) AS IntText,
    DATALENGTH(CONVERT(varchar(11), @LowestInt)) AS IntTextBytes,
    CONVERT(varchar(20), @LowestBigint) AS BigintText,
    DATALENGTH(CONVERT(varchar(20), @LowestBigint)) AS BigintTextBytes;

Always Write the Length

Omitting the length creates another avoidable ambiguity. CAST and CONVERT use a default character length of thirty. A varchar declaration without a specified length defaults to one. The same type name therefore does not imply the same width in both contexts.

Explicit lengths make review straightforward. They also reveal whether a size was chosen deliberately or inherited accidentally. For a text export, validate the fully formatted result before placing it in a narrower field. Include signs, separators, and any prefix in the required width.

DECLARE @DefaultDeclaration varchar = 'ABCDE';
DECLARE @ExplicitDeclaration varchar(5) = 'ABCDE';
SELECT @DefaultDeclaration AS DefaultDeclaredText,
    @ExplicitDeclaration AS ExplicitDeclaredText,
    CAST('ABCDE' AS varchar) AS DefaultCastText,
    CAST('ABCDE' AS varchar(5)) AS ExplicitCastText;

Repair the Expression Before Repairing the Rows

Rebuild affected values from the retained numeric source or other verified evidence. Do not replace every star with a guessed number. If the source is gone, mark the value unresolved and investigate the upstream record. The placeholder is not reversible encoding.

A short VARCHAR issue also needs regression checks at the conversion boundary. Compare the rebuilt text with the original numeric input, and test the entire destination path. Keep the original stored value during a controlled repair until validation is complete.

The safe habit is simple: size every conversion explicitly and verify that it preserves the intended value. A short VARCHAR can finish without an exception while losing the information you wanted. Treat successful execution and successful representation as separate checks.

Related reading on this blog: Truncating Data and ANSI_WARNINGS and SQL SERVER 2019: Still Getting String or Binary Data Would be Truncated.

A conversion star is not a shortened number, it is a sign that the number did not fit.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.