By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

extended character search

P: n/a
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?

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a

"PepperellMA" <an**@pepperell.net> wrote in message
news:11*********************@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
Jul 23 '05 #3

P: n/a
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


Jul 23 '05 #4

P: n/a

"PepperellMA" <an**@pepperell.net> wrote in message
news:11**********************@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
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.