Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove Decimal function

Status
Not open for further replies.

Mantle51

Programmer
Aug 17, 2006
97
US
Hello,

I'm trying to write a function that will output 10 characters and remove the decimal.

CREATE FUNCTION dbo.fnRemoveDecRate(@Rate VARCHAR(20))
RETURNS CHAR(10)

AS

BEGIN

RETURN
RIGHT(REPLICATE('0',10) + @Rate, 10)
CASE CHARINDEX(@Rate,char(46),1)
WHEN 0 THEN REPLACE(@Rate,CHARINDEX(@Rate,char(46)),'')
END
END

For the life of me, I cannot reconcile the VBA Instr return value with T-SQL's CharIndex()

Many Thanks.............
 
Code:
CREATE FUNCTION dbo.fnRemoveDecRate(@Rate VARCHAR(20))
RETURNS CHAR(10)

AS
  
RETURN
    RIGHT(REPLICATE('0',10) + REPLACE(@Rate,'.',''), 10)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
OK, I didn't know you can concatenate functions like that.
Thank you.......
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top