extended character search

Is there an easy way to perform a SELECT where you have

blah LIKE 'asdf'

but instead of just returning all the asdf's, it also looks for sdf,
sdf, sdf, etc?Right now, this is what I'm doing: replacing each accent letter (a, e,
i, etc) with a string of possible accents. For example

A search for 'GONCALTRONICA' actually searches with:

'G[o][n][c][a]LTR[o][n][i][c][a]'

and returns the correct row with 'GONALTRNICA' in the field. I'm
thinking there has to be a better way to have a case insensitive
search. Maybe an option somewhere?

Thanks|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106163716.326409.20730@.c13g2000cwb.googlegro ups.com...
> Right now, this is what I'm doing: replacing each accent letter (a, e,
> i, etc) with a string of possible accents. For example
> A search for 'GONCALTRONICA' actually searches with:
> 'G[o][n][c][a]LTR[o][n][i][c][a]'
> and returns the correct row with 'GONALTRNICA' in the field. I'm
> thinking there has to be a better way to have a case insensitive
> search. Maybe an option somewhere?
> Thanks

You can specify an accent-insensitive collation in your queries:

create table #pep (col1 nvarchar(100))
insert into #pep select 'GONALTRNICA'

-- Returns 0 rows
select * from #pep
where col1 = 'GONCALTRONICA'
-- Returns 1 row
select * from #pep
where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI

Simon|||I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
I am using an out of date version of sql server that doesn't support
COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

> You can specify an accent-insensitive collation in your queries:
> create table #pep (col1 nvarchar(100))
> insert into #pep select 'GONALTRNICA'
> -- Returns 0 rows
> select * from #pep
> where col1 = 'GONCALTRONICA'
> -- Returns 1 row
> select * from #pep
> where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI
>
> Simon|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106165927.837051.321340@.f14g2000cwb.googlegr oups.com...
> I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
> I am using an out of date version of sql server that doesn't support
> COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

COLLATE is only available in SQL 2000 - please always mention which version
you have (and to be fair, I shouldn't have assumed you had 2000). In SQL 7,
the sort order is fixed at install time, so you would have to rebuild the
master database to change it.

If it's important enough to you, it might be worth rebuilding (but of course
you run the risk of breaking other code), setting up an additional MSSQL
installation for insensitive searches, or upgrading to 2000, otherwise
you're probably stuck with writing code as you've already done. Fulltext
searching is always case and accent sensitive, so unfortunately that's not
an option either.

By the way, your build version indicates you haven't installed any
servicepacks (SP4 is the latest one for SQL 7).

Simon