Converting Words to Number in Sql Server

 

--select CHARINDEX('HUNDRED',replace(replace('FOUR HUNDRED FIFTY - ONE THOUSAND TWO HUNDRED THIRTY - FOUR','-',''),' ',''))

--select replace(replace(replace(replace('FOUR HUNDRED FIFTY - ONE THOUSAND TWO HUNDRED THIRTY - FOUR','-','|'),' ',''),'HUNDRED','|'),'THOUSAND','|')

DECLARE @count int ,@i int, @Finalno varchar(50),@no varchar(10)

Set @count = 0

set @i=0

set @Finalno=''

select @count = MAX(id) from SplitPIPESep(replace(replace(replace(replace(replace('ONE THOUSAND TWO HUNDRED ELEVEN','-','|'),' ',''),'MILLION','|'),'HUNDRED','|'),'THOUSAND','|'))

While @i < @count

Begin

    select @no = Convert(varchar(10),number)

    from (select id,seccontion from SplitPIPESep(replace(replace(replace(replace(replace('ONE THOUSAND TWO HUNDRED ELEVEN','-','|'),' ',''),'MILLION','|'),'HUNDRED','|'),'THOUSAND','|'))) a

    inner join NumTowordMaster  b on b.words = a.seccontion

    where a.id=@i+1   

    SET @Finalno = @Finalno + @no   

    Set @i= @i +1

end

    select @Finalno

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Narendra Kushwaha>

-- Create date: <Create Date, 29-10-2013>

-- Description:    <Description, ,>

-- =============================================

CREATE FUNCTION [dbo].[SplitPIPESep]

(

    -- Add the parameters for the function here

    @list nvarchar(MAX)

)

RETURNS varchar(max)

AS

BEGIN

    -- Declare the return variable here

    DECLARE @NUMBER varchar(max), @pos int, 

           @nextpos int, 

           @value varchar(10),@valuedig varchar(10)

 

   SELECT @pos = 0, @nextpos = 1 

 

   WHILE @nextpos > 0 

   BEGIN 

      SELECT @nextpos = charindex('|', @list, @pos + 1) 

      SELECT @valuedig = Convert(varchar(10),number) From NumTowordMaster Where words = LTRIM(Rtrim(@nextpos))

      SET @value = @value + @valuedig

      select @value

   END 

    -- Add the T-SQL statements to compute the return value here

    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function

    RETURN @NUMBER

END

GO

Data Contained in Table NumTowordMaster