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.
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.
Please check below although it is still halfway.
Supplemental information (FW/tool version etc.)
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
OS Windows Server 2012 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.
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.
- [oracle] i want to select records from three linked tables
- C # and JS implementation to remove duplicate records in textbox text box
- oracle - how many more do you say "the number of records is large"?
- oracle - delete duplicate data in table
- oracle - about sql without error
- oracle - about sql to remove duplicate rows
- Method for querying duplicate data records in MongoDB using aggregate
- The distinct statement in MySQL to query duplicate records and related performance discussions
- Oracle deletes duplicate data, Oracle data deduplication
- Method for eliminating duplicate records in database query
- oracle query without parentheses and methods without specified characters
- SQL statement to delete duplicate records and keep only one
- SQL to remove duplicate records (seven)
- How many different implementation codes does Java 1,2,3,4 have without duplicate numbers?
- ORACLE 10G Modified Character Encoding Without Restrictions on Super Character Set
- in oracle sql, i want to merge records that have overlapping periods and perform a select
- Example of deleting duplicate records in a data table in SQL Server
- SQL delete duplicate records in a table
- java - about extracting duplicate elements of arraylist
- Oracle how to standardize the cleaning of v $archived_log records
- mysql - sql update statement
- sql server - it's about sql injection
- db not started in oracle rac environment
- want to standardize the code that links php and db
- sql - how to express relations in surrogate keys
- create conditional expression using sql subquery
- oracle sql
- php - transaction with overwriting db table does not work mysql
- php - if there is data in the db table with mysql, please tell me how to insert if there is no update
- php - sql statement to get data at regular intervals from serial number in mysql