help with searching statement

[knuckle05]

Hi all,

I have a database full of music bands that I want to search on.

Everything is fine except that I would like to omit the word 'The' from my search. For ex:

When searching for The Beatles or The Doors or The Band, they should be classified under B, or D respectively, instead of under T.

This is the only word I want to omit. How can I replace this word with blank charcaters in my search string?

Thx!

 

[checkai]

Replace(STRING, 'The ', '')

 

[ClaireHCM]

replace(string,'the','') which will take away any song or band name with the in between.

 

[knuckle05]

Genius.

I posted here because I figured there were more syntax experts here.

I'm using Access. Same syntax?

 

[knuckle05]

Oh...also, I would only like to replace the word 'The' if it's at the beginning of the string!

 

[Pattycake245]

How about:

update table
set string = (select replace(string,'the','')) where left(ltrim(rtrim(string)),3)='the'

Not sure what the Access syntax is though.

Tim

 

[SQLBill]

SQL Server's Transact-SQL is not the same as MS Access's SQL. The basic syntax may be the same, but that's about it.

I suggest posting in the MS Access forum(s).

-SQLBill



SELECT 'forum' CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'