When you convert int to varchar it returns *. Why?

  • Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?

    You would think '3000' to be return. :unsure:

  • You can read on the CAST and CONVERT page on the "Truncating and Rounding Results" section.

    Int, smallint and tinyint will return * when the result length is too short to display when converted to char or varchar. Other numeric to string conversions will return an error.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • John Mihalko (7/25/2014)


    Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?

    You would think '3000' to be return. :unsure:

    Because SQL Server does not automatically do truncation on this type of conversion. The * represents an indication that the VARCHAR isn't large enough to hold the result.

    If you want the "right 4", then use RIGHT(263000,4) to do both the conversion and the leading truncation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (7/25/2014)


    John Mihalko (7/25/2014)


    Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?

    You would think '3000' to be return. :unsure:

    Because SQL Server does not automatically do truncation on this type of conversion. The * represents an indication that the VARCHAR isn't large enough to hold the result.

    If you want the "right 4", then use RIGHT(263000,4) to do both the conversion and the leading truncation.

    Thank you for your reply Jeff. Sorry for not being clear. I understand how to get the last four digits "RIGHT(263000,4)". I am just curious as to why the convert and cast function operates this way and I think you answered that question.

    The * represents an indication that the VARCHAR isn't large enough to hold the result.

    The asterisk result makes it clear that the VARCHAR is not large enough because if it returned '3000' the end user would think that this is an accurate and valid number.

    The back story is, I came across this maintaining someone else's SSIS solution. The cast statement was in the source component script. I don't convert in the source component. I always have a derived column component immediately after the source for conversions, trims, defaults, etc. I was just curious why the result was *. I am more curious why someone would convert in the source component in an SSIS package but that is a question for another forum. 🙂

  • John Mihalko (7/25/2014)


    Jeff Moden (7/25/2014)


    John Mihalko (7/25/2014)


    Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?

    You would think '3000' to be return. :unsure:

    Because SQL Server does not automatically do truncation on this type of conversion. The * represents an indication that the VARCHAR isn't large enough to hold the result.

    If you want the "right 4", then use RIGHT(263000,4) to do both the conversion and the leading truncation.

    Thank you for your reply Jeff. Sorry for not being clear. I understand how to get the last four digits "RIGHT(263000,4)". I am just curious as to why the convert and cast function operates this way and I think you answered that question.

    The * represents an indication that the VARCHAR isn't large enough to hold the result.

    The asterisk result makes it clear that the VARCHAR is not large enough because if it returned '3000' the end user would think that this is an accurate and valid number.

    The back story is, I came across this maintaining someone else's SSIS solution. The cast statement was in the source component script. I don't convert in the source component. I always have a derived column component immediately after the source for conversions, trims, defaults, etc. I was just curious why the result was *. I am more curious why someone would convert in the source component in an SSIS package but that is a question for another forum. 🙂

    My 2Cents

    😎

    You might want to reconsider this, SSIS literally sucks at type conversion compared to SQL Server. Also, sql scripts such as procedures, view and functions carry a lot less TOC on the longer term in terms of maintenance, re usability etc.

  • The reason I do not convert in the SSIS source component is that if the data type is a string and someone working the source system decides to expand the string length without telling anyone, if you convert in the SSIS source component the string will get truncated and the SSIS package will NOT fail. Months can go by before anyone realizes that a field holds a bunch of truncated data.

    I change data types in a derived column component so if a string length gets expanded, the derived column will fail on a truncation error and my data remains clean.

    I cannot count the number of times I have had to fix data because someone expanded a string in a source component, didn't tell anyone, and now our table column is filled with months of truncated data that we either can't fix because the original data is no longer in the source system or spend a ridiculous amount of time and money fixing the data field so the data is no longer truncated.

    • This reply was modified 2 weeks, 1 day ago by  John Mihalko.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply