String Compare

Question

Post reply

String Compare

ningaraju.ne 46825

Old Hand

Points: 353
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: 353 More actions · Answer 2

ningaraju.ne 46825

Old Hand

Points: 353

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: 183706 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: 61141 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: 353 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: 183706 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: 4333 More actions · Answer 8

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