Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

When you convert int to varchar it returns *. Why? Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:14 AM
Points: 3, Visits: 76
Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?

You would think '3000' to be return.
Post #1596345
Posted Friday, July 25, 2014 12:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 3,813, Visits: 8,578
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1596353
Posted Friday, July 25, 2014 12:21 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,403, Visits: 31,964
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.


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1596356
Posted Friday, July 25, 2014 12:44 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:14 AM
Points: 3, Visits: 76
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.


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.
Post #1596365
Posted Friday, July 25, 2014 1:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,253, Visits: 6,183
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.


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.
Post #1596375
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse