Query to select rows that don't partially match-CodePudding

I have the following table

STORE_ID|PRICE_1|PRODUCT_ID
-------- ------- ----------
    1052|   4.99|5157917035
    1052|   4.99|5157917035
    1052|   4.99|5157917036
    1052|   4.99|5157917036
    1052|   4.99|5157917037

As you can see these product IDs starts with "5157817". Is there a way to select only part of the value, in this case ignoring the last 3 digits and then filter out rows that are not distinct

CodePudding user response：

Is there a way to select only part of the value

Sure; usually, we use substr function. If column's datatype is one of the CHAR family, just apply it directly. Otherwise, if it is a NUMBER, first convert it to a string (using the to_char function).

For example:

SQL> create table test (col_n number, col_c varchar2(10));

Table created.

SQL> insert into test values (5157917035, '5157917035');

1 row created.

SQL> select substr(to_char(col_n), 1, 7) sub_n,
  2         substr(col_c, 1, 7) sub_c
  3  from test;

SUB_N                        SUB_C
---------------------------- ----------------------------
5157917                      5157917

SQL>

I didn't quite understand what result you expect out of data set you posted, but - if you ran e.g.

select DISTINCT store_id, 
                price_1,
                substr(product_id, 1, 7)
from your_table

you'd get only one row.