How to group data which not the same ?

[Golden Jing]

Dear All, First i would like to sorry if my expliant not clear. My issue is that I want group data in field company name which not equal each other. It caused by inputting different users. Example: Company_Name ComxxxA Co. ComxxxA Co. ComxxxA Co.,Ltd. ComxxxA Co.,Ltd. ComxxxA Co., ComxxxA Co., ComxxxA ComxxxA Comx ComxxxxxxxB Co.,Ldt ComxxxxxxxB Co.,Ldt ComxxxxxxxB Co.,Ldt ComxxxxxxxB Co.,Ldt ComxxxxxxxB Co ComxxxxxxxB Co., ComxxxxxxxB Co., ..... (there r alot company name which difference) So up to example above, what i want is the name is shortest ComxxxA Comx ComxxxxxxxB Co thanks for your help...

VB.Net

[Niladri_Biswas]

Try this

declare @t table(Company_Name varchar(50))
insert into @t
select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
select 'ComxxxA ' union all select 'ComxxxA ' union all
select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'

Query:

select Company_Name
from @t
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name

Output:

Company_Name
Comx
ComxxxA
ComxxxxxxxB Co

:)

Niladri Biswas

[Golden Jing]

Thanks, Yes it like example. But it is not enough yet. Because in this field has more companies like that up to differenc user enter difference. So have any solution for protect it ?

VB.Net

[Niladri_Biswas]

[Message Deleted]

[Golden Jing]

it is not complete what i want. but i would like to thank to you so much.

VB.Net

[Luc Pattyn]

Hi, you can use SELECT DISTINCT[^] and ORDERBY to get an ordered list of all different company names present. you can't find the "shortest" names as in your example, it would only give "Comx", not what you said it would. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---