0

This one is a little tricky, I hope I can make myself clear in this issue because is not very common problem (or maybe it is?).

I have a table that have duplicate records like this (i'm talking about hundreds):

|   Code|Route|State|City|Start| End|Style|
|-----------------------------------------|
|    R14|   14|   NL| MTY|  Ind|Main| High|
| R14-01|   14|   NL| MTY|  Ind|Main| High|
|  R15-1|   15|   NL| MTY|  Cal| Cle|  Low|
|   R15B|   15|   NL| MTY|  Cal| Cle|  Low|
|  R14-2|   14|   NL| MTY|  Ind|Main| High|
| RT15th|   15|   NL| MTY|  Cal| Cle| High|
|  RT15°|   15|   NL| MTY|  Cal| Cle| High|
|  R15.3|   15|   NL| MTY|  Cal| Cle|  Low|
| RT15/H|   15|   NL| MTY|  Cal| Cle| High|

I need to get the answer like this:

| Code|Route|State|City|Start| End|Style|
|---------------------------------------|
|  R14|   14|   NL| MTY|  Ind|Main| High|
|  R15|   15|   NL| MTY|  Cal| Cle|  Low|
| RT15|   15|   NL| MTY|  Cal| Cle| High|

I already create the query that group the results by Route, State, City, Start, End and Style; that was the very easy part.

SELECT DISTINCT Route, State, City, Start, End, Style FROM Routes;

If you can see the Code column is the only one that is causing problems. I need to group that column by similar Code (intersect characters and their positions R14, R14-01, R14-2 => R14 and R15-1, R15-2 => R15- and R15, R15-1 => R15)

Any idea how i can get those intersections?

To clarify the column Code is a mess, there are a lot characters that use as limitators. The table is not that short, I'm talking about thousands of records and some of them have that problem. I expanded the table a little so you can have a better view of what I'm tying to accomplish.

prueba prueba
  • 652
  • 1
  • 8
  • 26
  • you can use substring to split the code string by "-" see this [solution](https://stackoverflow.com/a/14951159/6227097) – Mohamed ِRadwan May 19 '18 at 02:14
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry May 19 '18 at 06:28

2 Answers2

0

You can do:

select (case when code in ('R14', 'R14-01', 'R14-2') then 'R14'
             when code in ('R15-1', 'R15-2') then 'R15-'
             when code in ('R15', 'R15-1') then 'R15'
             else code
        end) as newcode, Route, State, City, Start, End, Style
from t
group by newcode, Route, State, City, Start, End, Style;

I note that R15-1 is assigned to two categories.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Same general idea as Gordon's answer, slightly different detail.

select distinct case
when code like '%-' then code -- ends in hyphen
else substr(code, 1, 3) end thecode
, etc
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43