[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Why does not LIKE operator work on ntext values in my query batch !!! Please, check me

Orgil

3/26/2007 9:38:00 AM

/* This is only for example, but my database has to have many values
and queries like this. */

I have a table named "table2". Its create statement is below:
CREATE TABLE Table2 (
[id] int IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
[value] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

I execute two insert statements below:
INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
@id
INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
@id

The value under GemtelSev column of B_Dursgal has 4888 unicode
characters. Now Table2 has two rows and [value] column has same values
for each row.
Now I using LIKE operator to check these values of [value] column are
really same to each other:

SELECT t1.[id]
FROM Table2 t1,
( SELECT value FROM Table2 where [id] = 1
) t2
WHERE t1.[id] = 2 AND t1.value LIKE t2.value

---------------------------------------------
the result set has no row. Therefore I guess that LIKE operator
doesn't work on values that have more characters than 3000 or 4000.
IS MY GUESS TRUE?

Really appreciate any help and advice offered. Thank you.
Regards
Orgil.
5 Answers

Uri Dimant

3/26/2007 9:43:00 AM

0

Orgil
I have just did testing and it returns id=2

CREATE TABLE Table2 (
[id] int IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
[value] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO Table2 (value) SELECT N'???????????????????'
INSERT INTO Table2 (value) SELECT N'???????????????????'


SELECT t1.[id]
FROM Table2 t1,
( SELECT value FROM Table2 where [id] = 1
) t2
WHERE t1.[id] = 2 AND t1.value LIKE t2.value






"Orgil" <orgilhp@yahoo.com> wrote in message
news:1174901878.155358.25610@b75g2000hsg.googlegroups.com...
> /* This is only for example, but my database has to have many values
> and queries like this. */
>
> I have a table named "table2". Its create statement is below:
> CREATE TABLE Table2 (
> [id] int IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
> [value] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
>
> I execute two insert statements below:
> INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
> @id
> INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
> @id
>
> The value under GemtelSev column of B_Dursgal has 4888 unicode
> characters. Now Table2 has two rows and [value] column has same values
> for each row.
> Now I using LIKE operator to check these values of [value] column are
> really same to each other:
>
> SELECT t1.[id]
> FROM Table2 t1,
> ( SELECT value FROM Table2 where [id] = 1
> ) t2
> WHERE t1.[id] = 2 AND t1.value LIKE t2.value
>
> ---------------------------------------------
> the result set has no row. Therefore I guess that LIKE operator
> doesn't work on values that have more characters than 3000 or 4000.
> IS MY GUESS TRUE?
>
> Really appreciate any help and advice offered. Thank you.
> Regards
> Orgil.
>


Uri Dimant

3/26/2007 9:50:00 AM

0

Sorry, it should I have just done testing.....







"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23BK7ct4bHHA.588@TK2MSFTNGP06.phx.gbl...
> Orgil
> I have just did testing and it returns id=2
>
> CREATE TABLE Table2 (
> [id] int IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
> [value] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> INSERT INTO Table2 (value) SELECT N'???????????????????'
> INSERT INTO Table2 (value) SELECT N'???????????????????'
>
>
> SELECT t1.[id]
> FROM Table2 t1,
> ( SELECT value FROM Table2 where [id] = 1
> ) t2
> WHERE t1.[id] = 2 AND t1.value LIKE t2.value
>
>
>
>
>
>
> "Orgil" <orgilhp@yahoo.com> wrote in message
> news:1174901878.155358.25610@b75g2000hsg.googlegroups.com...
>> /* This is only for example, but my database has to have many values
>> and queries like this. */
>>
>> I have a table named "table2". Its create statement is below:
>> CREATE TABLE Table2 (
>> [id] int IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
>> [value] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> )
>>
>> I execute two insert statements below:
>> INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
>> @id
>> INSERT Table2 SELECT GemtelSev FROM B_Dursgal WHERE UNBMS_ID =
>> @id
>>
>> The value under GemtelSev column of B_Dursgal has 4888 unicode
>> characters. Now Table2 has two rows and [value] column has same values
>> for each row.
>> Now I using LIKE operator to check these values of [value] column are
>> really same to each other:
>>
>> SELECT t1.[id]
>> FROM Table2 t1,
>> ( SELECT value FROM Table2 where [id] = 1
>> ) t2
>> WHERE t1.[id] = 2 AND t1.value LIKE t2.value
>>
>> ---------------------------------------------
>> the result set has no row. Therefore I guess that LIKE operator
>> doesn't work on values that have more characters than 3000 or 4000.
>> IS MY GUESS TRUE?
>>
>> Really appreciate any help and advice offered. Thank you.
>> Regards
>> Orgil.
>>
>
>


Razvan Socol

3/26/2007 10:08:00 AM

0

Hello, Orgil

A value is not always like the same value. Consider this:

CREATE TABLE t (x varchar(10))

INSERT INTO t VALUES ('A')
INSERT INTO t VALUES (NULL)
INSERT INTO t VALUES ('[A]')
INSERT INTO t VALUES ('A_B%')

SELECT x FROM t WHERE x=x
SELECT x FROM t WHERE x LIKE x
SELECT x FROM t WHERE x NOT LIKE x

DROP TABLE t

As you can see, besides the NULL (which is not even equal to itself),
the value '[A]' is not LIKE itself, although it is equal to itself.

Razvan

Orgil

3/26/2007 10:09:00 AM

0

Thank you for your time Uri Dimant.

When I test it with short text values, it returns id 2. But when the
text value has huge number of characters (4850 for example), that
testing is not resulted !!!
If you have time, you would better to try the testing with the long
text value.

Uri Dimant

3/26/2007 10:25:00 AM

0

Orgil
I got the same result

INSERT INTO Table2 (value) SELECT REPLICATE(N'Å',4850 )
INSERT INTO Table2 (value) SELECT REPLICATE(N'Å',4850 )


SELECT t1.[id]
FROM Table2 t1,
( SELECT value FROM Table2 where [id] = 1
) t2
WHERE t1.[id] = 2 AND t1.value LIKE t2.value



"Orgil" <orgilhp@yahoo.com> wrote in message
news:1174903714.888103.102130@p77g2000hsh.googlegroups.com...
> Thank you for your time Uri Dimant.
>
> When I test it with short text values, it returns id 2. But when the
> text value has huge number of characters (4850 for example), that
> testing is not resulted !!!
> If you have time, you would better to try the testing with the long
> text value.
>