T-SQL function to convert numeric to words?

money in words in sql server
how to convert number into words using mysql query
sql convert varchar to decimal
convert varchar column to int in sql server
sql convert varchar to numeric
sql convert number to text
how to convert number into words using sql server query
convert function in sql

I am struggling to build a function that can convert a number 0-9 in a string into the spelling word of it. Here is what I have so far, and I realize 'word' is not a built in conversion, it's just what my thoughts on how to construct this would be:

CREATE FUNCTION [dbo].[fn_Numbers2Words]
    (@strText VARCHAR(1000))
RETURNS varchar(1000)
AS
BEGIN
    WHILE PATINDEX('%[0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[0-9]%', @strText), 1, CONVERT(word, PATINDEX('%[0-9]%', @strText)))
    END
    RETURN @strText
END

The idea would be to input something like this:

  SELECT [dbo].[fn_Numbers2Word]('1900testnumber')

and return this:

  'oneninezerozerotestnumber'

I have tried functions that do entire numbers, but since my strings will have alphas they do not work. I also tried incorporating those functions into this function above with no luck. I'm sure it's just something I'm doing syntax wise.

Can anyone help me alter my above function so that it produces my desired result?


Since you are just wanting a simple replacement then using nested replace would super crazy fast and simple. I would avoid using a scalar function here.

declare @strText varchar(1000) = '1900testnumber'

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@strText, '1', ' one'), '2', 'two'), '3', 'three'), '4', 'four'), '5', 'five'), '6', 'six'), '7', 'seven'), '8', 'eight'), '9', 'nine'), '0', 'zero')

How to convert Number to word using Sql Query with standard US , I need a function or query in Sql Server which is to convert Number to word in SQL Server in US or UK Format. I am struggling this. pls. help  Convert Numeric Figures into Words. How about including this in standard convert function !!! Have nice time Regards John. T-SQL; For most DBAs, normalization is an understood concept, a


Just for fun, here's another option that you could put into a function

Example

Declare @S varchar(max) = '1900testnumber'

Select @S = replace(@S,sFrom,sTo)
 From (values ('0','zero')
             ,('1','one')
             ,('2','two')
             ,('3','three')
             ,('4','four')
             ,('5','five')
             ,('6','six')
             ,('7','seven')
             ,('8','eight')
             ,('9','nine')
      ) A(sFrom,sTo)

Select @S

Returns

oneninezerozerotestnumber

convert number into words – SQLServerCentral, I want to convert number into words in sql server 2005.For examople,we enter number like 15000 from front end ,but it should be save into  I need a function or query in Sql Server which is to convert Number to word in SQL Server in US or UK Format. I am struggling this. By default, there isn't a built-in function to handle this so you would likely need to write your own stored procedure to handle performing it or you could consider performing it within your actual application when


Brute force with CHOOSE:

Returns the item at the specified index from a list of values in SQL Server.

DECLARE @num INT = 20;

SELECT CHOOSE(@num,
'one ',
'two ',
'three ',
'four ',
'five ',
'six ',
'seven ',
'eight ',
'nine ',
'ten',
'eleven ',
'twelve ',
'thirteen ',
'fourteen ',
'fifteen ',
'sixteen ',
'seventeen ',
'eighteen ',
'nineteen ',
'twenty',
'twenty-one ',
'twenty-two ',
'twenty-three ',
'twenty-four ',
'twenty-five ',
'twenty-six ',
'twenty-seven ',
'twenty-eight ',
'twenty-nine ',
'thirty',
'thirty-one ',
'thirty-two ',
'thirty-three',
'thirty-four ',
'thirty-five ',
'thirty-six ',
'thirty-seven ',
'thirty-eight ',
'thirty-nine ',
'forty',
'forty-one ',
'forty-two ',
'forty-three ',
'forty-four ',
'forty-five ',
'forty-six ',
'forty-seven ',
'forty-eight ',
'forty-nine ',
'fifty',
'fifty-one ',
'fifty-two ',
'fifty-three ',
'fifty-four ',
'fifty-five ',
'fifty-six ',
'fifty-seven ',
'fifty-eight ',
'fifty-nine ',
'sixty',
'sixty-one ',
'sixty-two ',
'sixty-three ',
'sixty-four ',
'sixty-five ',
'sixty-six ',
'sixty-seven ',
'sixty-eight ',
'sixty-nine ',
'seventy',
'seventy-one ',
'seventy-two ',
'seventy-three ',
'seventy-four ',
'seventy-five ',
'seventy-six ',
'seventy-seven ',
'seventy-eight ',
'seventy-nine ',
'eighty',
'eighty-one ',
'eighty-two ',
'eighty-three ',
'eighty-four ',
'eighty-five ',
'eighty-six ',
'eighty-seven ',
'eighty-eight ',
'eighty-nine ',
'ninety',
'ninety-one ',
'ninety-two ',
'ninety-three ',
'ninety-four ',
'ninety-five ',
'ninety-six ',
'ninety-seven ',
'ninety-eight ',
'ninety-nine'
)

DBFiddle Demo

CAST and CONVERT (Transact-SQL), Reference for the CAST and CONVERT Transact-SQL functions. An integer expression that specifies how the CONVERT function will translate in other words conversions that convert a data type from its original data type  Does anyone know how if it's possible to take an integer variable in SQL and convert it to the equivilent number of the form: First, Second, Third, Fourth etc? Thanks sql sql-server string numbers


SQL, can i convert a numbered amount into Words amount in Transact-SQL, ON GO CREATE FUNCTION [usp_Number2Words](@Number INT,  1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles. 2 The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).


Convert Numbers to Words in Sql Server?, Try Numbers to Words in C#, SQL Server and Crystal Reports[^] SQL SERVER – Query to Convert Numbers into Words [^]. If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { }) on the editor toolbar to nicely format and syntax highlight it! – marc_s Mar 16 '11 at 9:44


MS SQL Script: Convert Number to Words, Sql Server, --AUTHOR:TENG 04/07/2019 --SQL Version: MS SQL Server 2014 Express Edition --Instructions: --1)Go to Line 143 to try the converter --2)The  First Create a Function. Create FUNCTION [dbo].[OneToNinetynine] ( @num int) RETURNS VARCHAR(50) AS BEGIN DECLARE @word VARCHAR(50) set @word='' if @num =1 set @word='One' else if @num =2 set @word='Two' else if @num = 3 set @word='Three' else if @num = 4 set @word='Four' else if @num = 5 set @word='Five' else if @num = 6 set @word='Six' else if @num = 7 set @word='Seven' else if @num = 8 set