April 13, 2015 at 4:39 pm
Im having a problem I have to use the cast function to display my freight amount for instance if the freight amount is 65.83 I need to display (the freight cost is 65 dollars and 83 cents) I have no idea how to display words any tips would be appreciated thanks.
April 13, 2015 at 4:45 pm
CAST is not needed. At least for what you posted.
SELECT *,
'the freight cost is ' + REPLACE(cost, '.', ' dollars and ') + ' cents'
FROM (VALUES(65.83)) x(cost)
April 14, 2015 at 5:43 am
Nice one Luis. I would have thought SQL would try and make everything numeric.
April 14, 2015 at 9:32 am
The magic is in the REPLACE() which will implicitly convert the number into a string when it receives the data. Other string functions such as LEFT(), RIGHT(), LEN() and others would do the same. Some like SUBSTRING() will fail.
And to expand a little bit, when you try to concatenate a number to a string, you need to convert (either implicitly or explicitly) the number into a string or SQL Server will try to convert the string into a number and add it. To know how implicit conversions will work and avoid surprises, use the following reference: https://msdn.microsoft.com/en-us/library/ms190309.aspx
April 14, 2015 at 9:42 am
i will certainly check that out but the requirement was to experiment with the cast function so i will keep trying thankyou
April 14, 2015 at 9:46 am
Luis Cazares (4/13/2015)
CAST is not needed. At least for what you posted.
SELECT *,'the freight cost is ' + REPLACE(CAST(cost AS VARCHAR(20)), '.', ' dollars and ') + ' cents'
FROM (VALUES(65.83)) x(cost)
You can try it this way:-D
April 14, 2015 at 9:56 am
karodhill (4/14/2015)
i will certainly check that out but the requirement was to experiment with the cast function so i will keep trying thankyou
CAST is very simple :-). You'll have more fun with CONVERT and the format codes.
April 14, 2015 at 11:36 am
I appreciate the effort you took to show me examples The last one worked for me thanks so much
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply