String Compare

Question

Post reply

String Compare

ningaraju.ne 46825

Old Hand

Points: 303
More actions
November 19, 2015 at 12:28 am

#309707

Hi All,

I have two strings that need to be compared.

DECLARE @C NVARCHAR(10),@R NVARCHAR(10)

SELECT @C=N'１２'

SELECT @R=N'12'

SELECT ASCII(@C),ASCII(@R)

IF @R=@C

BEGIN

SELECT 1

END

it is returning true but we require it as false.I tried with ACSII and both have the same value

Regards,

Ningaraju N E

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

What about this

DECLARE @C NVARCHAR(10),@R NVARCHAR(10)

SELECT @C=replace(replace(N'1 2',char(9),' '),char(13),' ')

SELECT @R=replace(replace(N'12',char(9),' '),char(13),' ')

SELECT ASCII(@C),ASCII(@R)

IF @R=@C

BEGIN

SELECT 1

END

There are invisible characters in the @C variable: tab or endof line char.

Igor Micev,
My blog: www.igormicev.com

ningaraju.ne 46825 Old Hand Points: 303 More actions · Answer 2

ningaraju.ne 46825

Old Hand

Points: 303

November 19, 2015 at 2:58 am

#1840910

Thanks Buddy...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Use UNICODE() instead of ASCII():

DECLARE @C NVARCHAR(10),@R NVARCHAR(10)



SELECT @C=N'12'

SELECT @R=N'12'

SELECT @C, UNICODE(@C), @R, UNICODE(@R)

-- don't forget that you are only comparing the first character of each string.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

I would try to use an adequate collation for the comparison.



DECLARE @C NVARCHAR(10),@R NVARCHAR(10)

SELECT @C=N'12'

SELECT @R=N'12'

SELECT UNICODE(@C),UNICODE(@R), @C, @R

IF @R = @C COLLATE Latin1_General_Bin

BEGIN

    SELECT 1

END

GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

This is where a Tally Table is a good thing. I run into this kind of problem and want to know why 2 strings are not equal. This would probably make for a useful UDF.

DECLARE @C NVARCHAR(10) = N'12 ',



        @R NVARCHAR(10) = N'12';

WITH

L1 AS (SELECT X FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) X(X)),

iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L1 a,L1 b,L1 c,L1 d)

SELECT TOP (CASE

              WHEN DATALENGTH(@C) >= DATALENGTH(@R)

			  THEN DATALENGTH(@C) ELSE DATALENGTH(@R)

            END)

  Position = N,

  c  = SUBSTRING(@C,N,1),

  r  = SUBSTRING(@R,N,1),

  cu = UNICODE(SUBSTRING(@C,N,1)),

  ru = UNICODE(SUBSTRING(@R,N,1))

FROM iTally

WHERE ISNULL(UNICODE(SUBSTRING(@C,N,1)),-1) <> ISNULL(UNICODE(SUBSTRING(@R,N,1)),-1);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ningaraju.ne 46825 Old Hand Points: 303 More actions · Answer 6

Thanks Buddy,

But still issue exists as it fails when we compare normal strings.

DECLARE @C NVARCHAR(10),@R NVARCHAR(10)

SELECT @C=N'Ant'

SELECT @R=N'ant'

IF @R = @C COLLATE Latin1_General_Bin

SELECT 1

ELSE

SELECT 2

As per my understanding Latin1_General_Bin is case sensitive and I searching for case insensetive.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 7

ningaraju.ne 46825 (11/24/2015)

Thanks Buddy,

But still issue exists as it fails when we compare normal strings.

DECLARE @C NVARCHAR(10),@R NVARCHAR(10)

SELECT @C=N'Ant'

SELECT @R=N'ant'

IF @R = @C COLLATE Latin1_General_Bin

SELECT 1

ELSE

SELECT 2

As per my understanding Latin1_General_Bin is case sensitive and I searching for case insensetive.

Well, it would be nice if we know all the rules. Why is it "1" different from "1" but "a" is equal to "A"?

EDIT: By the way, I still believe that the appropriate collation is what's needed. Probably this one? Latin1_General_CI_AI_KS_WS (Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Scott-144766 SSCarpal Tunnel Points: 4238 More actions · Answer 8

Scott-144766

SSCarpal Tunnel

Points: 4238

November 24, 2015 at 8:19 am

#1842077

From Books Online (bold emphasis mine). Is this your problem?

ASCII (Transact-SQL)

Returns the ASCII code value of the leftmost character of a character expression.

--
Scott

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Scott-144766 (11/24/2015)

From Books Online (bold emphasis mine). Is this your problem?

ASCII (Transact-SQL)

Returns the ASCII code value of the leftmost character of a character expression.

Not really, he was warned of this five days ago 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden