What I modify on code to make two value temperature equal

Question

1.00/5 (1 vote)

See more:

I work on sql server 2019 i compare value between two table
it display as
125℃
125°C
it display two values different although it seem it is equal
what query can i write it to make both values equal

select [dbo].[RemoveSpecialCharacters]('125℃')
select [dbo].[RemoveSpecialCharacters]('125°C')

both must be equal
so what i modify on my code to be equal please

What I have tried:

ALTER Function [dbo].[RemoveSpecialCharacters](@Input VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-zA-Z0-9 ]%'
    While PatIndex(@KeepValues, @Input) > 0
        Set @Input = Stuff(@Input, PatIndex(@KeepValues, @Input), 1, '')

    Return @Input
End

Posted 24-Oct-22 1:56am

ahmed_sa

Updated 24-Oct-22 4:19am

Add a Solution

Comments

Member 15627495 24-Oct-22 8:18am

it's °C which is a different string between the two values.
use the left(var,length) / right(var,length ) function to keep only 'numbers' value

ahmed_sa 24-Oct-22 8:33am

so can there are any function can make both values equal
these two value are equal
can i add any function can make both equal

Richard MacCutchan 24-Oct-22 9:39am

Why not just store the values as integers rather than strings? The database field should determine the temperature scale to be used.

ahmed_sa 24-Oct-22 10:17am

if i need both equal how to write statment make both two value equal
according to me both are equal
so how to make both statment equal please

ahmed_sa 24-Oct-22 10:23am

can you show me what you mean ?

Richard MacCutchan 24-Oct-22 10:43am

If you wish to reply to a comment then please use the Reply button above the message. Just adding more comments gives no clue as to who you are responding to.

1 solution

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)

OriginalGriff · Answer 1 · 2022-10-24T04:19:00

Basically, if you want to compare numbers, store them as numbers in a numeric field: have a INT column which holds a Centigrade temperature and convert the input from whatever the user (or other source) provides to a simple integer.
It's more efficient, and a lot less prone to errors like this - as well as being simpler to internationalize to include Fahrenheit input / output at a later date.

If you had done that to start with, you problem would have never occurred: treh first example doesn't include a "C" but a similar looking character which isn't in your function's "Keep" set. Because the second example does include a "C" you end up comparing "125" with "125C" and finding they don't match.

Always store values in the most appropriate format or things get silly quickly when you don't expect them to. For example, sorting strings doesn't give the same order as a numeric sort: "125C" is less than "35C" because the result of the whole comparison is based on the first different pair of characters from the left hand side - no others are looked at. Since the first difference is '1' and '3' SQL considers that "125C" is less than "35C" and orders data "wrongly" as a result.