How to remove the infinity(∞) value from a string in SQL query

Question

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

Quote:
Select Replace(@Data,'∞','9999') Data

I'm getting the following result

Quote:
Day 9999 - Day 9999

Actually My result would be

Quote:
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?

Thanks,
Arunkumar Murugesan

What I have tried:

I can able to achieve this with C# with linq query. But here I need in SQL.

Posted 22-Nov-21 0:55am

Arunkumar Murugesan

Updated 22-Nov-21 1:32am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2021-11-22T01:28:00

Solution 1

Your data needs to be nvarchar, not varchar. If it's varchar, the character ∞ will be replaced with the character 8.

SQL

DECLARE @Data nvarchar(50) = N'Day 8 - Day ∞';
SELECT Replace(@Data, N'∞', N'9999') As Data;
-- Output: Day 8 - Day 9999

Posted 22-Nov-21 1:28am

Richard Deeming

Comments

Maciej Los 22-Nov-21 7:33am

5ed!

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.

Arunkumar Murugesan 22-Nov-21 9:55am

Okay. Let me try for solution. Thanks.

Maciej Los · Accepted Answer · 2021-11-22T01:32:00

Solution 2

Take a look here:

SQL

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

Result:

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

SQL Server 2019 | db<>fiddle[^]

Posted 22-Nov-21 1:32am

Maciej Los