How to insert comma delimited values in one column in oracle?-CodePudding

I have a table by the name of personal_info and it contains id,name and phone_number as columns. So the following is table structure which I want to store data.

id	name	phone_number
1	ali	03434444, 03454544, 0234334

So how to store data in phone_number column in comma delimited format and how to filter that column in where clause for example

Select * from personal_info where phone_number = 03454544 ;

And which datatype is suitable for phone_number column.

CodePudding user response：

VARCHAR2 is suitable for phone numbers. You can get the values this way:

WITH personal_info AS
(
  SELECT 1 AS ID, 'Ali' AS NAME, '03434444, 03454544, 0234334' AS phone_number FROM dual
)
SELECT *
  FROM (SELECT id, name, TRIM(regexp_substr(phone_number, '[^,] ', 1, LEVEL)) AS phone_number
          FROM personal_info 
       CONNECT BY LEVEL <= LENGTH (phone_number) - LENGTH(REPLACE(phone_number, ',' ))   1)
 WHERE phone_number = '03454544';

CodePudding user response：

Wrong data model, it isn't normalized. You should create a new table:

create table phones
  (id_phone     number constraint pk_phone primary key,
   id_person    number constraint fk_pho_per references person (id_person),
   phone_number varchar2(30) not null
  );

Then you'd store as many numbers as you want, one-by-one (row-by-row, that is).

If you want to do it your way, store it just like that:

insert into person (id, name, phone_number)
values (1, 'ali', '03434444, 03454544, 0234334');

One option of querying such data is using the instr function:

select * from person
where instr(phone_number, '03434444') > 0;

or like:

select * from person
where phone_number like '%'% || '03434444' || '%'

or split it into rows:

select * from person a
where '03434444' in (select regexp_substr(b.phone_number, '[^,] ', 1, level)
                     from person b
                     where b.id_person = a.id_person
                     connect by level <= regexp_count(b.phone_number, ',')   1
                    )

I'd do it my way, i.e. with a new table that contains only phone numbers.

CodePudding user response：

Well, the real good practice would rather be to have another table PHONE with a 1xN association (for example a PHONE_ID primary key, and ID and PHONE columns.)

You may then have the result you want with a view based on your two tables and using the LISTAGG operator : https://fr.wikibooks.org/wiki/Oracle_Database/Utilisation_de_fonctions/fonction_LISTAGG, but this will be much efficient to work with, especially if you want WHERE clauses based on your phone numbers.

CodePudding user response：

Use LIKE with the delimiters:

Select *
from   personal_info
where  ', ' || phone_number || ', ' LIKE '%, ' || '03454544' || ', %';

However

You should consider changing your data structure to store the phone numbers in a separate table:

CREATE TABLE phone_numbers (
  person_id    REFERENCES personal_info (id),
  phone_number VARCHAR2(12)
);

And then you can get the data using a JOIN

SELECT pi.*,
       pn.phone_number
FROM   personal_info pi
       INNER JOIN phone_numbers pn
       ON (pi.id = pn.person_id)
WHERE  pn.phone_number = '03434444'

or, if you want all the phone numbers:

SELECT pi.*,
       pn.phone_numbers
FROM   personal_info pi
       INNER JOIN (
         SELECT person_id,
                LISTAGG(phone_number, ', ') WITHIN GROUP (ORDER BY phone_number)
                  AS phone_numbers
         FROM    phone_numbers
         GROUP BY person_id
         HAVING COUNT(CASE WHEN phone_number = '03434444' THEN 1 END) > 0
       ) pn
       ON (pi.id = pn.person_id)

db<>fiddle here