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.
July 25, 2014 at 12:21 pm
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
Change is inevitable... Change for the better is not.
July 25, 2014 at 12:44 pm
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. 🙂
July 25, 2014 at 1:01 pm
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.
January 11, 2022 at 9:59 pm
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.
February 7, 2022 at 10:07 pm
Someone needs a "dope-slap" !!
"... someone expanded a string in a source component, didn't tell anyone, ..."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply