Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on sql server 2019 i compare value between two table
it display as
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)
    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
Updated 24-Oct-22 4:19am
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 ?

1 solution

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.
Share this answer
Richard MacCutchan 24-Oct-22 10:44am    
Well spotted.

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