#Postgresql string contains substring full

For our purposes, we’ll specify what we want trim to remove-the double quotes. How can we remove this? In one of our other articles, we learned how you can use the SQL TRIM function to remove unwanted characters. If we take a close look at the result, we can see an opening double quote before each drug name. In our example, we want the first element of the list, so we pass in 1 as the third argument. The second argument is the delimiter (a comma), and the third is the position of the desired element. The first is the name of the field-in this case, drugs_in_receipt. The PostgreSQL split_part function takes three arguments. SELECT split_part(drugs_in_receipt, ',',1) as first_medicine We’ll use the PostgreSQL split_part function to extract only the first drug name from each record. As we already mentioned, these drug names are comma separated, like “aspirin, paracetamol”. This field stores a list of drugs prescribed by the doctor for each case. Suppose the first drug in each list is the main drug the patient will need to take, and our doctor wants to extract the main drug from each list under the drugs_in_receipt column. Full_NameĮxtracting a Substring from a String with PostgreSQL SPLIT_PART If we take a look at the database below, we can see a list of comma-separated drugs under that column. We’ll mainly focus on the drugs_in_receipt column.

#Postgresql string contains substring full

The table contains the patient’s full name, the date of their visit, the doctor’s diagnosis, the treatment the doctor suggested, and any drugs the doctor prescribed.Įvery time a patient visits his office, the doctor creates a new record and manually types his notes into the database. In this article, we’ll mainly focus on extracting parts of a string for further analysis.įor a better understanding, we’ll work with a table with sample health care data created by a doctor who stores all his patient information in this single table.

In another article titled “How to Deal with Unwanted Characters in SQL”, we took a look at how common SQL string functions can help us control the quality of our data.

There are many reasons why you’d want to use SQL string functions such as split_part in PostgreSQL in your database, but the main reason is to address common data quality issues. Why Use String Functions in Your Database?