Search a non english word using 'contains'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Search a non english word using 'contains'

Author

Topic

enb141
Starting Member

14 Posts

Posted - 2004-06-21 : 11:12:39

Hi I have a problem when I do a search on a non english word for example acción well in that case what I do is this:

Select * from T_SUCURSALES where nombre like('%acci[oó]n%')

but the problem comes when I try to find two different non continus words, in order to do a two words seach what I do is this:

Select * from T_SUCURSALES where contains (Nombre, '"*Editor*" AND "*Grupo*" AND "*ACCION*"') order by nombre

the good thing about contains is that I can search all those words (or starting/ending with) but unfortunely I can't use [oó] in contains so what I have to do is this:

Select * from T_SUCURSALES where contains (Nombre, '"*Editor*" AND "*Grupo*" AND "*ACCION*" AND "*ACCIÓN*"') order by nombre

but I don't wanna add twice the non english word to my search, can anyone help me with that please

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-21 : 18:24:13

Couldn't you use:

SELECT *
   FROM T_SUCURSALES
   WHERE  Contains (Nombre, '"*Editor*" AND "*Grupo*")
      AND Contains(Nombre, '"*ACCI[OÓ]N*"')
   ORDER BY nombre

-PatP

enb141
Starting Member

14 Posts

Posted - 2004-06-21 : 19:27:24

I tryed but it didn't search both words (Acción) and (Accion) :(

[oó] seems to work only with "like" but not with "Contains" :(

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-22 : 12:37:26

Ok, let's turn this problem on its ear and try things a slightly different way. What if we change the collation to make accents irrelevant? Something like:

SELECT *
   FROM T_SUCURSALES
   WHERE  Contains (Nombre COLLATE SQL_Latin1_General_Cp850_CI_AI
,     '"*Editor*" AND "*Grupo*" AND "*ACCION*")
   ORDER BY nombre

Give that a try and see what you get.

-PatP

Kristen
Test

22859 Posts

Posted - 2004-06-22 : 13:30:25

Is listing the derived words a possiblity?


SELECT *
   FROM T_SUCURSALES
   WHERE  Contains (Nombre, '"*Editor*" AND "*Grupo*")
      AND Contains(Nombre, '"*ACCION*" OR "*ACCIÓN*"')
   ORDER BY nombre

Kristen

enb141
Starting Member

14 Posts

Posted - 2004-06-22 : 13:46:27

SELECT *
FROM T_SUCURSALES
WHERE Contains (Nombre COLLATE SQL_Latin1_General_Cp850_CI_AI , '"*Editor*" AND "*Grupo*" AND "*ACCION*"')
ORDER BY nombre

the SQL gave me this error

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'COLLATE'.

enb141
Starting Member

14 Posts

Posted - 2004-06-22 : 13:54:32

quote:
Originally posted by Kristen

Is listing the derived words a possiblity?

SELECT *
FROM T_SUCURSALES
WHERE Contains (Nombre, '"*Editor*" AND "*Grupo*")
AND Contains(Nombre, '"*ACCION*" OR "*ACCIÓN*"')
ORDER BY nombre

Kristen

I know it works but unfortunelly I can't use it because in spanish some people when wanna do a search they only write the word with or without accent like upper or lower case so in spanish, italian an those latin languages is hard to do searchs because of accents (tilde in spanish)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-22 : 15:49:12

How many accents are there?

Maybe you can do something along the lines of:

Select text from myTable Where contains(replace(text,'Ó','O'),'"*Editor*" AND "*Grupo*" AND "*ACCION*"')

Corey

enb141
Starting Member

14 Posts

Posted - 2004-06-22 : 16:59:29

quote:
Originally posted by Seventhnight

How many accents are there?

Maybe you can do something along the lines of:

Select text from myTable Where contains(replace(text,'Ó','O'),'"*Editor*" AND "*Grupo*" AND "*ACCION*"')

Corey

SQL gave this error

Select * from T_SUCURSALES Where contains(Replace(nombre,'Ó','O'),'"*Editor*" AND "*Grupo*" AND "*ACCION*"')

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Do you know what that error means?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-22 : 17:52:59

try:

Select *
From (Select *, nNombre = Replace(nNombre,'Ó','O') from T_SUCURSALES) as A
Where contains(nombre,'"*Editor*" AND "*Grupo*" AND "*ACCION*"')

Corey

enb141
Starting Member

14 Posts

Posted - 2004-06-22 : 19:24:48

quote:
Originally posted by Seventhnight

try:

Select *
From (Select *, nNombre = Replace(nNombre,'Ó','O') from T_SUCURSALES) as A
Where contains(nombre,'"*Editor*" AND "*Grupo*" AND "*ACCION*"')

Corey

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'nNombre'.
Server: Msg 7601, Level 16, State 1, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'A' because it is not full-text indexed.

I will never get it working

enb141
Starting Member

14 Posts

Posted - 2004-06-24 : 10:24:40

Well what I did was this

Select * from T_SUCURSALES where nombre like ('%Editor%') and nombre like ('%Accion%')

with the help of Pat Phelan thanks for your tip using SQL_Latin1_General_Cp850_CI_AI in nombre

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-24 : 14:49:09

Kewlski! Glad that I could help.

-PatP

enb141
Starting Member

14 Posts

Posted - 2004-06-24 : 17:30:26

Yes, thanks again to all you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources