Click here to Skip to main content
15,566,065 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team,

Is there any option to remove the Infinity(∞) value from a string in SQL Query?

For Example,

My String is 'Day 8 - Day ∞'

Here I have to replace my string value as 'Day 8 - Day 9999'

When I try to write the following query
Select Replace(@Data,'∞','9999') Data
I'm getting the following result
Day 9999 - Day 9999
Actually My result would be
Day 8 - Day 9999
Here SQL consider ASCII value of 8 and ∞ both are same value.

Could you please suggest any other way to replace Infinity symbol alone from the string in SQL?

Arunkumar Murugesan

What I have tried:

I can able to achieve this with C# with linq query. But here I need in SQL.
Updated 22-Nov-21 2:32am

Your data needs to be nvarchar, not varchar. If it's varchar, the character will be replaced with the character 8.
DECLARE @Data nvarchar(50) = N'Day 8 - Day ∞';
SELECT Replace(@Data, N'∞', N'9999') As Data;
-- Output: Day 8 - Day 9999
Share this answer
Maciej Los 22-Nov-21 7:33am    
Arunkumar Murugesan 22-Nov-21 7:49am    
Thank you so much for your reply. But here we have to add Unicode(N')? Since I'm passing the User defined table from C# to SQL stored procedure. In how the string pass as Unicode?
Richard Deeming 22-Nov-21 8:10am    
Make sure your SQL data types are using nvarchar instead of varchar; use the N prefix on any string literals within your SQL query; and make sure you're not specifying the non-unicode string types for your parameters in C#.
Arunkumar Murugesan 22-Nov-21 9:11am    
But before replacing the N the infinity changed as 8.
Declare @Input nvarchar(100) = 'Day 8 - Day ∞'
Select @Input Input -- The Result as Day 8 - Day 8 Here I cannot add prefix before processing the replace option
Richard Deeming 22-Nov-21 9:14am    
Because you are not using nvarchar for your string literals!

When you try to stuff a Unicode character into a non-Unicode string literal, weird things happen. One of those weird things is that turns into 8.

Use Unicode/nvarchar string literals: prefix the literal with N, and I told you in my solution.

If you can't use a Unicode/nvarchar literal, then you can't replace a non-Unicode character.
Take a look here:

SELECT REPLACE(Data, N'∞', '9999')
FROM tmp

Day 8 - Day 9999
Day 4 - Day 9999
Day 2 - Day 9999

SQL Server 2019 | db<>fiddle[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900