CODEWITHSUNDEEP
sqloracle-database
Kate
Kate

Reputation: 455

Group by -SQL ORACLE

My column contains these values.

ABCD INC.
ABCD INC REGISTERED 
ABCD ORD SHS
ABCD ORD REGISTERED

How can I use correct group by using substring function so my distinct values looks like this?

ABCD

Upvotes: 1

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can extract the string up to the first space. One method uses regexp_substr():

select regexp_substr(col, '[^ ]+'), count(*)
from t
group by regexp_substr(col, '[^ ]+');

This is a bandage on your real problem, which is having multiple versions of the same name. You should really implement a look-up table that maps the column values to a canonical name. This generally requires manual maintenance, but that is the cost of maintaining data integrity.

Upvotes: 3

Related Questions