Home>

I want to extract duplicate data from the corresponding table.
At that time, I would like to make a condition not to distinguish between "half-width" and "full-width" of the value of a specific column.

Learn more

Given the following table:

■ Product table

Manufacturer Use Application Type Model Number Sales Start Date Stock
Adidas | Soccer Equipment | Spike | AAA | 2018/10/10 | Exist
Adidas | Soccer Equipment | Spike | AAA | 2018/08/10 | Exist
Adidas | Baseball Equipment | Bad | BBB | 2018/06/01 | Yes
Nike | Soccer Equipment | Spike | CCC | 2018/05/01 | None
Nike | Baseball Equipment | Gloves | DDD | 20/08/2018 | None
Nike | Baseball Equipment | Bad | EEE | ​​04/05/2018 | Yes
Puma | Land Goods | Jersey | FFF | 2018/03/01 | Yes
Puma | Land Goods | Wear | GGG | 02/01/2018 | Yes
ASICS | Land Goods | Jersey | HHH | 2017/12/01 | Yes
ASICS | Land Goods | Wear | FFF | 10/06/2018 | Yes

The conditions to be extracted are as follows.
-Records with the same manufacturer, application, type, model number, and sales start date
・ Model numbers are not case-sensitive, uppercase/lowercase alphabetic characters, alphanumeric characters/half-width katakana characters, etc.
 
 
In this case, the records on the 1st and 2nd lines are applicable.

Corresponding SQL statement

Please check below although it is still halfway.

SELECT
  *
FROM
  Product table T1
  LEFT JOIN (
    SELECT
      Manufacturer
      , Applications
      , Type
      , Model number
      , Sales start date
      , COUNT (*) AS CNT
    FROM
      Product table
    GROUP BY
      Manufacturer
      , Applications
      , Type
      , Model number
      , Sales start date
  ) T2
    ON T1.Manufacturer = T2.Manufacturer
    AND T1. Usage = T2. Usage
    AND T1.Type = T2.Type
    AND T1. Model number = T2. Model number
    AND T1. Sales start date = T2. Sales start date
WHERE
  T2.CNT>1
Supplemental information (FW/tool version etc.)

[DB]
OS Windows Server 2012 R2
Oracle11g R2

I think it's an inappropriate SQL statement
I do not understand the sentence that satisfies the extraction condition.

  • Answer # 1

    A past question.
    https://StackOverflow.com/questions/79442#reply-124958

    Is this not enough?

  • Answer # 2

    There is no full-width/half-width conversion function, so use TRANSLATE to convert it.
    If the usage frequency is high, it is convenient to create a store that returns the conversion string.

  • Answer # 3

    UPPER and LOWER are used to convert uppercase and lowercase letters using methods other than regular expressions. TO_SINGLE_BYTE and TO_MULTI_BYTE can be used for alphanumeric single-byte multibyte conversion.

    UTL_I18N can be used for Hiragana and Katakana conversions depending on the Oracle version.

    Typically design your tables so that you don't need to do this conversion.