[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server nested replace statement

Posted on 2012-09-04
11
Medium Priority
?
1,015 Views
Last Modified: 2012-09-07
replace((replace(testColumn,1,'one'),2,'two').....,12,'twelve')

so what i am trying to do is display 12 as twelve. But to my surprise, I am getting value of 'onetwo'. Similarly, 11 would display as 'oneone' instead of 'eleven'. Any solutions?

I even tried doing replace((replace(testColumn,'1','one'),'2','two').....,'12','twelve'), putting quotes around the integers. no luck
0
Comment
Question by:itbossman
11 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38364181
You could tack on a space to the right of each value, then RTRIM it to remove the last space

RTRIM(replace((replace(testColumn,1,'one '),2,'two ').....,12,'twelve '))
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1000 total points
ID: 38364205
if you write in reverse order it works. first write replace for 12 then for 11 then ... 2,1,0:


replace(replace(replace(replace(YourColumn,'12','twelve'),'11','eleven'),'10','ten'),'9','nine')

Open in new window

0
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 1000 total points
ID: 38364207
try reversing the order of the replaces ie replace 12 before 11, etc

RTRIM(replace((replace(testColumn,12,'twelve'),11,'eleven ').....,1,'one'))
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 38364210
declare @test nvarchar(500)
set @test='112'
select REPLACE(REPLACE(REPLACE(@test,12,'twelve'),2,'two'),1,'one')

Open in new window


Use this sample code to your code

RTRIM(replace((replace(testColumn,12,'twelve'),2,'two ').....,1,'one')) 

Open in new window

0
 
LVL 31

Expert Comment

by:hnasr
ID: 38364234
Try this for a table A(a Int) using Common Table Expression (CTE)
a      
1      
2      
3      
11      
12      

with nos (num, numText)
As 
(
Select 1, 'one'
union
select 2, 'two'
union
Select 3, 'three'
union
select 4, 'four'
union
Select 5, 'five'
union
select 6, 'six'
union
Select 7, 'seven'
union
select 8, 'eight'
union
Select 9, 'nine'
union
select 10, 'ten'
union
Select 11, 'eleven'
union
select 12, 'twelve')
select a, numText from A inner join nos on A.a=nos.num;

Open in new window


Result:
a      numText
1      one
2      two
3      three
11      eleven
12      twelve
0
 
LVL 18

Expert Comment

by:vasto
ID: 38364336
Just replace value onetwo with twelve

replace((replace(testColumn,1,'one'),2,'two').....,'onetwo','twelve')
However if you want to convert a number into words this replacement will be not enough because you will need to handle also hundreds, thousands etc.

Check this link for a function that can do this:
http://www.sqlservercentral.com/Forums/Topic794134-149-1.aspx
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38364431
here's my example:
just nest replace function on to others:
declare @date varchar(20);
set @date=substring(replace(cast( CONVERT(varchar(35), GETDATE(), 127) as varchar),'-','_'),0,14)--':','_')
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38364621
the following function do it completely:
CREATE FUNCTION NumberInEnglish (@Number AS BIGINT)
RETURNS VARCHAR(1024)
AS BEGIN
    DECLARE @Below20 TABLE
        (
         ID INT IDENTITY(0, 1),
         Word VARCHAR(32)
        )
    DECLARE @Below100 TABLE
        (
         ID INT IDENTITY(2, 1),
         Word VARCHAR(32)
        )
    INSERT  @Below20 (Word)
    VALUES  ('Zero'),
            ('One'),
            ('Two'),
            ('Three'),
            ('Four'),
            ('Five'),
            ('Six'),
            ('Seven'),
            ('Eight'),
            ('Nine'),
            ('Ten'),
            ('Eleven'),
            ('Twelve'),
            ('Thirteen'),
            ('Fourteen'),
            ('Fifteen'),
            ('Sixteen'),
            ('Seventeen'),
            ('Eighteen'),
            ('Nineteen')
    INSERT  @Below100
    VALUES  ('Twenty'),
            ('Thirty'),
            ('Forty'),
            ('Fifty'),
            ('Sixty'),
            ('Seventy'),
            ('Eighty'),
            ('Ninety')
    DECLARE @English VARCHAR(1024) = (SELECT    CASE WHEN @Number = 0 THEN ''
                                                     WHEN @Number BETWEEN 1 AND 19 THEN (SELECT Word
                                                                                         FROM   @Below20
                                                                                         WHERE  ID = @Number
                                                                                        )
                                                     WHEN @Number BETWEEN 20 AND 99 THEN (SELECT    Word
                                                                                          FROM      @Below100
                                                                                          WHERE     ID = @Number / 10
                                                                                         ) + '-' + dbo.NumberInEnglish(@Number % 10)
                                                     WHEN @Number BETWEEN 100 AND 999
                                                     THEN (dbo.NumberInEnglish(@Number / 100)) + ' Hundred ' + dbo.NumberInEnglish(@Number % 100)
                                                     WHEN @Number BETWEEN 1000 AND 999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000)) + ' Thousand ' + dbo.NumberInEnglish(@Number % 1000)
                                                     WHEN @Number BETWEEN 1000000 AND 999999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000000)) + ' Million ' + dbo.NumberInEnglish(@Number % 1000000)
                                                     WHEN @Number BETWEEN 1000000000 AND 999999999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000000000)) + ' Billion ' + dbo.NumberInEnglish(@Number % 1000000000)
                                                     WHEN @Number BETWEEN 1000000000000 AND 999999999999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000000000000)) + ' Trillion ' + dbo.NumberInEnglish(@Number % 1000000000000)
                                                     WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000000000000000)) + ' Quadrillion ' + dbo.NumberInEnglish(@Number % 1000000000000000)
                                                     WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999
                                                     THEN (dbo.NumberInEnglish(@Number / 1000000000000000000)) + ' Quintillion ' + dbo.NumberInEnglish(@Number % 1000000000000000000)
                                                     ELSE ' INVALID INPUT'
                                                END
                                     )
 
    SELECT  @English = RTRIM(@English)
    SELECT  @English = RTRIM(LEFT(@English, LEN(@English) - 1))
    WHERE   RIGHT(@English, 1) = '-'
    RETURN (@English)
   END
GO
 

Open in new window


Example:
SELECT  dbo.NumberInEnglish(18)

Open in new window

0
 
LVL 9

Expert Comment

by:keyu
ID: 38367297
You have two ways,,,

if you want to go with your specified code simply put 12 ansd 11 before defining 1 or 2 in replace command

RTRIM(replace(replace(testColumn,12,'twelve'),11,'eleven ')....)

or you must need to create one function and simply call it inside your sql statement which is more better option as per user friendlynes and readability..

its general function..

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords.htm
0
 

Author Closing Comment

by:itbossman
ID: 38377418
thank you. reversing order worked
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 38377504
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question