SQL Search for Unicode text does not work

Hi,

Here are some examples I found
http://stackoverflow.com/questions/11258129/sql-server-search-in-nvarchar-ntext
http://stackoverflow.com/questions/3543570/ntext-comparison

so, what does this return?
      SELECT Text as Verses FROM AB
            Where Text like N'%¿¿¿¿ ¿¿ ¿¿¿¿¿%'

Can you convert the ntext column to nvarchar( max )?

HTH
  David

SELECT Text as Verses FROM AB
            Where Text like N'%¿¿¿¿ ¿¿ ¿¿¿¿¿%'

Gives me every Text in the table.  As if I am doing just the first line with no Where clause.

I changed ntext Nvarchar(max) but no difference.

Hi,

Can you post some sample data?

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.AB' )
		and type in( N'U' )
	)
	drop table dbo.AB
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.AB' )
		and type in( N'U' )
	)
	create table dbo.AB (
		id int identity
		, text nvarchar( max )
	) on [PRIMARY]
go

insert dbo.AB( text ) 
	values( 'There was a lady from niger' )
	, ( 'Who smiled as she rode on a tiger' )
	, ( 'They went for a ride' )
	, ( 'With the lady inside' )
	, ( 'And the smile on the face of the tiger' )
;

select *
from dbo.AB
;

select ab.text
from dbo.AB
where
	ab.text like N'%tiger%'
;

Select allOpen in new window

gives

id          text
----------- ------------------------------------------------------------------------------------------------------------------
1           There was a lady from niger
2           Who smiled as she rode on a tiger
3           They went for a ride
4           With the lady inside
5           And the smile on the face of the tiger

(5 row(s) affected)

text
------------------------------------------------------------------------------------------------------------------------------
Who smiled as she rode on a tiger
And the smile on the face of the tiger

(2 row(s) affected)

Select allOpen in new window

HTH
  David

When I posted the Unicode phrase in my initial question, it came out as bunch of  inverted question marks.  You can see similar unicode I am working with at this site.
http://www.lexilogos.com/keyboard/amharic.htm
 You can actually type the fonts yourself and get some unicode texts.

I will try to post some sample text again:  I can see it here in this blog now, I am not sure if you can see it.  It is in Ethiopic Charachters.

¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿

Here is the sql query I am testing.

SELECT * FROM AB
            Where Text like N'%¿¿¿%'

Hi,

I've demonstrated that the basic query is sound. Obviously I don't have a collation that is Ethiopic, so you'll need to post the exact collation you are using, and if necessary you'll have to use the character codes.

Regards
  David

I used your exact code above with my unicode Text.  When I use my text without a prefix N'...' for insert caluse, I get question marks inserted in the table.  With the N prefix it gets in the table in a readable format.  However the 2 select querys at the end, both give all five entries.  I tried to attach the font but .ttf is not allowed file type.  Here is my code.

use AmhB
go

if exists(
      select *
      from sys.objects
      where
            object_id = object_id( N'dbo.AB' )
            and type in( N'U' )
      )
      drop table dbo.AB
;
go

if not exists(
      select *
      from sys.objects
      where
            object_id = object_id( N'dbo.AB' )
            and type in( N'U' )
      )
      create table dbo.AB (
            id int identity
            , text nvarchar( max )
      ) on [PRIMARY]
go

insert dbo.AB( text )
      values(N'¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿ ¿¿ ¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿ ¿¿ ¿¿¿¿¿¿ ¿¿¿ ')
,(N'¿¿¿¿ ¿¿¿ ¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿ ¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿ ')
,(N'¿¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿ ')
,(N'¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿;')
,(N'¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿ ¿¿¿¿¿ ¿¿ ¿¿¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿¿¿ ¿¿¿')

;

select *
from dbo.AB
;

select ab.text
from dbo.AB
where
      ab.text like N'%¿¿¿¿%'

Hi,

Sorry but Ethiopic doesn't paste.

Either a screen-shot, or attach the query, preferably both.

Regards
  David

I have attached the screen shot.  I don't know why the query would have problem recognizing the unicode text but the DB table saves it in a reqadable format.
You can notice that the search text in the 'Like' is a phrase from the first inserted line.
UnicodeTextQuery.JPG

David,

Sorry but Ethiopic doesn't paste.
I think you mean Ethiopian.  :)

Hi,

@Anthony - I was copying and pasting how the asker described it.

@ateshome - what collation are you using?

Regards
  David

The called the Fonts 'Ethiopic'

Hi,

Note the font. The collation that is the default for the server or the database.

As in, in SSMS, right click on the server, select properties. In the general tab, near the bottom is a line
Server Collation. Mine is Latin1_General_CI_AS.
What is yours?

Regards
  David

Mine is SQL_Latin1_General_CP1_CI_AS

Thanks

I was copying and pasting how the asker described i
You are right, I had not noticed.

Hi,

Latin1 ... collation is very similar to your SQL_Latin1 ... collation.

Can you please post a list of the character codes for each phrase. Use the Unicode function.
http://msdn.microsoft.com/en-us/library/ms180059.aspx

Regards
  David

I have atttached the screen shot for the result I got when I plugged my first unicode line in the Unicode function you pointed to.
The Message tab just says:
Character # Unicode Character UNICODE Value

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)....
Unicode-Screen.JPG

Hi ateshome

Thank you for that. I've been able to get that into a string and table in a meaningful form.

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.AB' )
		and type in( N'U' )
	)
	drop table dbo.AB
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.AB' )
		and type in( N'U' )
	)
	create table dbo.AB (
		id int identity
		, text nvarchar( max )
	) on [PRIMARY]
go

insert dbo.AB( text ) 
	values( 'There was a lady from niger' )
	, ( 'Who smiled as she rode on a tiger' )
	, ( 'They went for a ride' )
	, ( 'With the lady inside' )
	, ( 'And the smile on the face of the tiger' )
;

print 'expected result'
select ab.text
from dbo.AB
where
	ab.text like N'%tiger%'
;

declare @nstring nvarchar( 20 )
set @nstring = isnull( @nstring, '' ) + nchar( 4821 )
set @nstring = isnull( @nstring, '' ) + nchar( 4829 )
set @nstring = isnull( @nstring, '' ) + nchar( 4651 )
set @nstring = isnull( @nstring, '' ) + nchar( 4637 )

print 'Ethiopian string'
select @nstring

insert dbo.AB( text ) select convert( nvarchar( max ), @nstring )

print 'This works without wildcards'
select *
from dbo.AB
where
	text like @nstring
;

print 'This doesn''t work'
select *
from dbo.AB
where
	ab.text like N'¿¿¿¿%'
;

print 'This does work'
select *
from dbo.AB
where
	ab.text like N'¿¿¿¿%' collate latin1_general_bin
;

Select allOpen in new window

note that the query that works if changing the collation of the test (ab.text like N'¿¿¿¿%' ) to a binary collation instead of the more usual case insensitive one.

My testing showed that it works with literals (shown above) or strings like:

declare @SearchString nvarchar( max )
set @SearchString = N'tiger'

select ab.text
from dbo.AB
where
	ab.text like @SearchString
;

-- works for tiger
set @SearchString = N'%' + @SearchString

select ab.text
from dbo.AB
where
	ab.text like @SearchString
;

-- works
set @SearchString = @nstring

select ab.text
from dbo.AB
where
	ab.text like @SearchString
;

-- does work
set @SearchString = N'%' + @nstring
print @SearchString

select ab.text
from dbo.AB
where
	ab.text like @SearchString collate latin1_general_bin
;

Select allOpen in new window

HTH
  David

collate latin1_general_bin works indeed!
I give you 6* out of 5, for Genius and perseverance (Stick-to-it-veness)

Thanks a lot!