Group By (part of a field)

In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
But for a unknown reason, maybe laziness, my users fill in all data in
Model.
How can i Group on the text before the first space i like to Group.
Examples:
Mondeo Gls
Galaxy 2.0
C220 2.0 D
C220 2.5 tdi
Scenic 2.0
Scenic 2.2
320 TDS
320 TD
So i have groups like Mondeo, Galaxy, C220, Scenic and 320
How can i do this ?
Thx for your help
GL.Well, I would suggest you clean up your data, and consider making domains
(likely in related tables) for each of those columns. Frankly I am amazed
that you dont have the following data:
Mondo Gls
Galaxy_2.0
C220 2.0 D
C22 2.5 tdi
Schenic 2.0
Scenic 2.2
3200 TDS
320 TD
So I am kind of thinking that this has to be more than just user entered
data. The answer to your problem on a temporary basis (since you have
indicated that they all have an initial space as a seperator is:
declare @.table table (value varchar(30))
insert into @.table
select 'Mondeo Gls'
union all
select 'Galaxy 2.0'
union all
select 'C220 2.0 D'
union all
select 'C220 2.5 tdi'
union all
select 'Scenic 2.0'
union all
select 'Scenic 2.2'
union all
select '320 TDS'
union all
select '320 TD'
select left(value,charindex(' ', value)) as model, count(*)
from @.table
group by left(value,charindex(' ', value))
model
-- --
320 2
C220 2
Galaxy 1
Mondeo 1
Scenic 2
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:GuZEf.233382$KY3.7519286@.phobos.telenet-ops.be...
> In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
> But for a unknown reason, maybe laziness, my users fill in all data in
> Model.
> How can i Group on the text before the first space i like to Group.
> Examples:
> Mondeo Gls
> Galaxy 2.0
> C220 2.0 D
> C220 2.5 tdi
> Scenic 2.0
> Scenic 2.2
> 320 TDS
> 320 TD
> So i have groups like Mondeo, Galaxy, C220, Scenic and 320
> How can i do this ?
> Thx for your help
> GL.
>|||DECLARE @.SomeCar VARCHAR(100)
SET @.SomeCar = 'Scenic 2.2'
SELECT
SUBSTRING(@.SomeCar, 1,CHARINDEX(CHAR(32),@.SomeCar)-1)
YOu can group by this expression.
HTH, Jens Suessmeyer.|||Thanx for your help. I have take over some parts and this is what works fine
for me.
SELECT left(carModel,charindex(' ',carModel)) as model
From tblVehicles
GROUP BY left(carModel,charindex(' ',carModel))")|||"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:w1_Ef.233447$UW5.7562339@.phobos.telenet-ops.be...

> Thanx for your help. I have take over some parts and this is what works
> fine for me.
> SELECT left(carModel,charindex(' ',carModel)) as model
> From tblVehicles
> GROUP BY left(carModel,charindex(' ',carModel))")
Yes, but you should SERIOUSLY consider cleaning up your data, and amending
the poor design of your GUI so that your users are forced to enter values in
the correct fields.