Script of the day
-
Suggestion: A bit of testing for posted scripts that aer published.
SSC issue --> Handling Zero Byte Files (SQLServerCentral.com 3/23/2006)
The script of the day was --> Convert Currency to English.
I've found lots of useful tidbits but this one was way too easy to break. Given the following input:
SELECT dbo.ConvertHundreds('787.55'),'787.55'
SELECT dbo.ConvertTens('87.55'),'87.55'
SELECT dbo.ConvertDigit('9.05'),'9.05'
(which is just a bit different than the provided input)
provides the following (slightly erroneous) results:
---------------------------------------------------------------- ------
Hundred Fifty Five 787.55
(1 row(s) affected)
---------------------------------------------------------------- -----
Eighty Five 87.55
(1 row(s) affected)
---------------------------------------------------------------- ----
9.05
(1 row(s) affected)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
This was removed by the editor as SPAM
-
Hi,
We designed a SSIS package which produces two flat file with different extensions.
the generation of file is based on a conditional split which directs the records to respective file. the problem i am facing is that if the condition split has records for only one file then the other file is also gets created but with Zero byte. I am wondering is there a way in SSIS to avoid this zero byte size file creation.
Thanks
Bunty
-
Hi
Re the number conversion.
At the risk of exposing myself to public humiliation, I wrote a script which does this for integers, but I figured that the bit after the decimal point was just more integers but with a dot in front
Although it is based on ideas I have seen on various other websites, none of those seemed to work very well. I look forward to receiving corrections and improvements. After I had created this, I was told the application didn't require this feature
e.g.:
SELECT dbo.ctfn_NumberToWords(1787.55) + ' DOLLARS ' + dbo.ctfn_NumberToWords((1787.55 - CAST(1787.55 AS INT)) * 100) + ' CENTS.'
gives
ONE THOUSAND SEVEN HUNDRED AND EIGHTY SEVEN DOLLARS FIFTY FIVE CENTS.
David
/*----------------------------------------------------------------------------------------------------------------------
Name: ctfn_NumberToWords
Version: 1.0
Date: 21-Feb-2007
Description: Takes an integer and converts it to words by storing the
integer into a table three digits at a time as hundreds, tens
and a factorial multiplier 'units'.
Returns a VARCHAR(2000) string containing the text of the
number, e.g.: ONE THOUSAND ONE HUNDRED AND TWENTY FOUR
Returns MINUS for a minus number and NULL if there is an error
Usage: SELECT dbo.ctfn_NumberToWords(INTEGER)
----------------------------------------------------------------------------------------------------------------------
History
1.0 DLQ 21-02-2007 RFC001428 Function created for ATPs
----------------------------------------------------------------------------------------------------------------------*/
CREATE FUNCTION ctfn_NumberToWords (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT
-- Create table of number groups
DECLARE @tblNumberGroups TABLE (Units SMALLINT, Hundreds SMALLINT, Tens SMALLINT)
-- Handle errors and 'quick wins'
IF @intNumberValue IS NULL RETURN NULL
IF ISNUMERIC(@intNumberValue)=0 RETURN NULL
IF @intNumberValue = 0 RETURN 'ZERO'
IF @intNumberValue < 0
BEGIN
SET @strReturn='MINUS '
SET @intNumberValue=ABS(@intNumberValue)
END
SET @intUnits =0
-- Populate table of number groups
WHILE (@intNumberValue % 1000) > 0 OR (@intNumberValue/1000) >0
BEGIN
INSERT INTO @tblNumberGroups (Units, Hundreds, Tens) VALUES (@intUnits, (@intNumberValue % 1000)/100, (@intNumberValue % 1000) % 100 )
SELECT @intNumberValue = CAST (@intNumberValue / 1000 AS INTEGER)
SET @intUnits = @intUnits + 1
END
-- Remove last unit added
SET @intUnits = @intUnits-1
-- Concatenate text number by reading number groups in reverse order
SELECT @strReturn = ISNULL(@strReturn,' ') +
ISNULL(
ISNULL((CASE Hundreds
WHEN 1 THEN 'ONE HUNDRED '
WHEN 2 THEN 'TWO HUNDRED '
WHEN 3 THEN 'THREE HUNDRED '
WHEN 4 THEN 'FOUR HUNDRED '
WHEN 5 THEN 'FIVE HUNDRED '
WHEN 6 THEN 'SIX HUNDRED '
WHEN 7 THEN 'SEVEN HUNDRED '
WHEN 8 THEN 'EIGHT HUNDRED '
WHEN 9 THEN 'NINE HUNDRED '
END),' ') +
CASE WHEN (Hundreds >0 OR Units<@intUnits) AND Tens > 0 THEN ' AND ' ELSE ' ' END +
ISNULL((CASE Tens / 10
WHEN 2 THEN 'TWENTY '
WHEN 3 THEN 'THIRTY '
WHEN 4 THEN 'FORTY '
WHEN 5 THEN 'FIFTY '
WHEN 6 THEN 'SIXTY '
WHEN 7 THEN 'SEVENTY '
WHEN 8 THEN 'EIGHTY '
WHEN 9 THEN 'NINETY '
END),' ') +
ISNULL((CASE Tens
WHEN 10 THEN 'TEN '
WHEN 11 THEN 'ELEVEN '
WHEN 12 THEN 'TWELVE '
WHEN 13 THEN 'THIRTEEN '
WHEN 14 THEN 'FOURTEEN '
WHEN 15 THEN 'FIFTEEN '
WHEN 16 THEN 'SIXTEEN '
WHEN 17 THEN 'SEVENTEEN '
WHEN 18 THEN 'EIGHTEEN '
WHEN 19 THEN 'NINETEEN '
END),' ') +
COALESCE(
CASE WHEN Tens %10 =1 AND Tens / 10 <> 1 THEN 'ONE ' END,
CASE WHEN Tens %10 =2 AND Tens / 10 <> 1 THEN 'TWO ' END,
CASE WHEN Tens %10 =3 AND Tens / 10 <> 1 THEN 'THREE ' END,
CASE WHEN Tens %10 =4 AND Tens / 10 <> 1 THEN 'FOUR ' END,
CASE WHEN Tens %10 =5 AND Tens / 10 <> 1 THEN 'FIVE ' END,
CASE WHEN Tens %10 =6 AND Tens / 10 <> 1 THEN 'SIX ' END,
CASE WHEN Tens %10 =7 AND Tens / 10 <> 1 THEN 'SEVEN ' END,
CASE WHEN Tens %10 =8 AND Tens / 10 <> 1 THEN 'EIGHT ' END,
CASE WHEN Tens %10 =9 AND Tens / 10 <> 1 THEN 'NINE ' END,
' ')+
COALESCE(
CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN 'THOUSAND ' END,
CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN 'MILLION ' END,
CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN 'BILLION ' END,
CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN 'TRILLION ' END,
' ')
,' ')
FROM @tblNumberGroups
ORDER BY units DESC
-- Get rid of all the spaces
WHILE CHARINDEX(' ', @strReturn)>0
BEGIN
SET @strReturn = REPLACE(@strReturn,' ',' ')
END
SET @strReturn = LTRIM(RTRIM(@strReturn))
RETURN @strReturn
END
If it ain't broke, don't fix it...
-
PS:
I stopped at Trillion because I didn't know what came next...
If it ain't broke, don't fix it...
-
Here is a link if you ever have some 'spare time'
(yeah, right) if you ever want to go over a trillion ...http://g42.org/tiki/tiki-index.php?page=BigNumbers
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I'm sure Steve has plenty of time to test all the scripts that are submitted
Seriously though, if it's that much of an issue, out of a community of over 450,000 members surely we'll have a plethora of able bodied volunteers to give Steve a hand to do the vetting
--------------------
Colt 45 - the original point and click interface
-
Aha,
I see from the link that as I am a Brit, I should insist on the proper use of 'Milliard' for 109 and 'Billion' for 1012.
David
If it ain't broke, don't fix it...
-
My father told me for years that a British billion was 1012, not 109. Noone here in Britain actually seems to use this system though.
Matt ChandlerSoftware test engineer,Red Gate Software
-
Without wishing to provoke discussion
, I think you have globalisation (i.e. the U.S.) to thank for that.My dad told me the same, he was very passionate about that sort of thing, mind you, he was against the S.I. unit standards as well !
If it ain't broke, don't fix it...
-
We definitely can't test the scripts. Barely enough time to get the editorials, QODs, etc. Even if we did test them, we wouldn't be able to test them exhaustively.
We are working to add discussions and voting to the scripts so people can post issues.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply