Solved

SQL Search for Unicode text does not work

Posted on 2014-02-17
20
499 Views
Last Modified: 2014-02-21
I have been trying to search for a Unicode text from a Stored Procedure but search doesn't work.  The table has Unicode text that is defined as ntext (I have also tried NVarchar).  When I search for a phrase in the table, I just don't get any match.  If I use 'Like', I get the whole table as a match.
   Here is the kind of search I was trying:
Declare @SearchPhrase NVarchar(max) = N'¿¿¿¿ ¿¿ ¿¿¿¿¿%'
      SELECT Text as Verses FROM AB
            Where Text like @SearchPhrase

This is how the Table is designed:
CREATE TABLE [dbo].[AB](
      [BookNumber] [int] NOT NULL,
      [Chapter] [int] NOT NULL,
      [Verse] [int] NOT NULL,
      [Text] [ntext] NOT NULL

I have also tried nvarchar(max) for Text

Both cases give me all Text in the table, whether the phrase is in Text or not
0
Comment
Question by:ateshome
  • 9
  • 9
  • 2
20 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39866600
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
0
 

Author Comment

by:ateshome
ID: 39866649
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.
0
 

Author Comment

by:ateshome
ID: 39868593
I changed ntext Nvarchar(max) but no difference.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39868686
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%'
;

Open 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)

Open in new window


HTH
  David
0
 

Author Comment

by:ateshome
ID: 39869123
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'%¿¿¿%'
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39869219
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
0
 

Author Comment

by:ateshome
ID: 39871008
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'%¿¿¿¿%'
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39871170
Hi,

Sorry but Ethiopic doesn't paste.

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

Regards
  David
0
 

Author Comment

by:ateshome
ID: 39871292
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39872010
David,

Sorry but Ethiopic doesn't paste.
I think you mean Ethiopian.  :)
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 35

Expert Comment

by:David Todd
ID: 39872024
Hi,

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

@ateshome - what collation are you using?

Regards
  David
0
 

Author Comment

by:ateshome
ID: 39872030
The called the Fonts 'Ethiopic'
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39872042
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
0
 

Author Comment

by:ateshome
ID: 39872083
Mine is SQL_Latin1_General_CP1_CI_AS

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39875727
I was copying and pasting how the asker described i
You are right, I had not noticed.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39875739
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
0
 

Author Comment

by:ateshome
ID: 39876006
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39877772
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
;

Open 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
;

Open in new window

HTH
  David
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39877787
EE mangled the Ethiopian characters - it looked good in edit and preview. Anyway, here is a screenshotScreenshot showing Ehtiopian characters in query
0
 

Author Closing Comment

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

Thanks a lot!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now