select distinct table names in SQL Server7.0 1

[rtshort]

I have a database in which a lot of the tables have similar names. For example a table named :

"76 Mercury Starter"
"76 Mercury Engine"
"76 Mercury Exhaust"
"98 Ford Starter"
"98 Ford Engine"
"98 Ford Exhaust"

and so forth.

How would I select Distinct table names from the database and only get one Mercury and one Ford in the query results?

I have tried "select Distinct Name from sysobjects where type like 'U'" but it still comes up with all of the tables. Any help is greatly appreciated.
Rob
Just my $.02.

 

[vishnuprasad]

The problem u have is that there are number values along with character in table name.

This can help u build some more ideas.
select * from sysobjects where difference(('client'),ltrim(name)) > 3
The above uses a difference which will give a relative measure.As the measure value increases the inference is that name matches more.

select * from sysobjects where name like '%client%'

Pls go thro some string functions in SQL BOL which may help u.

 

[rtshort]

Thanks for the reply. I'll have a look at SQL BOL. Your post however, pointed me in the right direction.
Rob
Just my $.02.