how to loop an array in string in a where clause-CodePudding

I have an information table with a column of an array in string format. The length is unknown starting from 0. How can I put it in a where clause of PostgreSQL?


* hospital_information_table
| ID  | main_name  | alternative_name  |
| --- | ---------- | ----------------- |
| 111 | 'abc'      | 'abe, abx'        |
| 222 | 'bbc'      | ''                |
| 333 | 'cbc'      | 'cbe,cbd,cbf,cbg' |


* record
| ID  | name    | hospital_id  |
| --- | ------- | ------------ |
| 1   | 'abc-1' |              |
| 2   | 'bbe 2' |              |
| 3   | 'cbf*3' |              |

e.g. this column is for alternative names of hospitals. let's say e.g. 'abc,abd,abe,abf' as column Name and '111' as ID. And I have a record with a hospital name 'cbf*3' ('3' is the department name) and I would like to check its ID. How can I check all names one by one in 'cbe,cbd,cbf,cbg' and get its ID '333'?

--update--

In the example, in the record table, I used '-', '*', ' ', meaning that I couldn't split the name in the record table under a certain pattern. But I can make sure that some of the alternative names may appear in the record name (as a substring). something similar to e.g. 'cbf' in 'cbf*3'. I would like to check all names, if 'abe' in 'cbf*3'? no, if 'abx' in 'cbf*3'? no, then the next row etc.

--update--

Thanks for the answers! They are great! For more details, the original dataset is not in alphabetic languages. The text in the record name is not separable. it is really hard to find a separator or many separators. Therefore, for the solutions with regrex like '[-* ]' could not work here.

Thanks in advance!

CodePudding user response：

You could use regexp_split_to_array to convert the coma-delimited string to a proper array, and then use the any operator to search inside it:

SELECT r.*, h.id
FROM   record r
JOIN   hospital_information h ON 
       SPLIT_PART(r.name, '-', 1) = ANY(REGEXP_SPLIT_TO_ARRAY(h.name, ','))

SQLFiddle demo

CodePudding user response：

Substring can be used with a regular expression to get the hospital name from the record's name.

And String_to_array can transform a CSV string to an array.

SELECT 
  r.id as record_id
, r.name as record_name
, h.id as hospital_id
FROM record r
LEFT JOIN hospital_information h
  ON SUBSTRING(r.name from '^(.*)[ *\-]\w $') = ANY(STRING_TO_ARRAY(h.alternative_name,',')||h.main_name)
WHERE r.hospital_id IS NULL;

record_id	record_name	hospital_id
1	abc-1	111
2	bbe 2	222
3	cbf*3	333

Demo on db<>fiddle here

Btw, text [] can be used as a datatype in a table.