CODEWITHSUNDEEP
sqlpostgresql
Sameer Anand
Sameer Anand

Reputation: 11

Is there a way in Postgres / SQL to substitute characters in strings from columns where the characters in the string are column names?

I have a table called sentences, and a table called logs.

The sentences table looks like this:

|------------|---------------------------------------|
|     id     |                sentence               |
|------------|---------------------------------------|
|     1      |     [var1] says hello!                |
|------------|---------------------------------------|
|     2      |     [var1] says [var2]!               |
|------------|---------------------------------------|
|     3      |     [var1] says [var2] and [var3]!    |
|------------|---------------------------------------|
|     4      |     [var4] says [var2] to [var1]!     |
|------------|---------------------------------------|

The logs table looks like this:

|------------|------------------|--------------|--------------|--------------|--------------|
|     id     |     sentenceId   |     var1     |     var2     |     var3     |     var4     |
|------------|------------------|--------------|--------------|--------------|--------------|
|     1      |         1        |     Sam      |              |              |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     2      |         2        |     Joe      |   what's up  |              |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     3      |         3        |     Tim      |     hey      | how are you  |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     4      |         4        |     Joe      |     hi       |              |    Tiffany   |
|------------|------------------|--------------|--------------|--------------|--------------|

The result I am trying to get is:

|------------|-----------------------------------------|
|  logs.id   |            sentences.sentence           |
|------------|-----------------------------------------|
|     1      |   [Sam] says hello!                     |
|------------|-----------------------------------------|
|     2      |   [Joe] says [what's up]!               |
|------------|-----------------------------------------|
|     3      |   [Tim] says [hey] and [how are you]!   |
|------------|-----------------------------------------|
|     4      |   [Tiffany] says [hi] to [Joe]          |
|------------|-----------------------------------------|

I'm not sure how to write the SQL query to make the database do the text substitutions for me.

I could just select everything from both tables using an inner join, and then loop through in code and do the substitutions myself. I.e.:

SELECT logs.id, sentences.sentence, logs.var1, logs.var2, logs.var3, logs.var4 FROM logs INNER JOIN sentences ON logs.sentenceId = sentences.id

And then in code:

logs.forEach(log => log.sentence.replace(/\[(.*?)\]/g, ($matchedString, $columnName) => log[$columnName] ))

But if possible, I'd like the database to do that for me so that I don't have to select more data than I need.

Upvotes: 1

Views: 121

Answers (2)

Belayer
Belayer

Reputation: 14861

Elegance is always nice, but sometimes brute force gets it done.

with logsnn (sentenceid, var1, var2, var3,var4) as
    ( select sentenceid 
           , coalesce(var1,'')  
           , coalesce(var2,'')  
           , coalesce(var3,'')  
           , coalesce(var4,'')
        from logs
    )  
select s.id
     ,(replace(replace(replace(replace(s.sentence 
        , '[var1]',l.var1)
        , '[var2]',l.var2)
        , '[var3]',l.var3)
        , '[var4]',l.var4)
      )  AS sentence
  from sentences s  
  left join logsnn l
        on l.sentenceid = s.id;

If you really need the brackets on the result change the replacement settings to

'[var1]','[' || l.var1 || ']')

The answer by @JNevill is close to the same, bit I believe that one will return Null if any of var1,var2,var3, or var4 are Null. The CTE here changes Null with the empty string. Postgres does not consider the empty string the same as null.

Upvotes: 0

user330315
user330315

Reputation:

I would write a function to do that: 

create function replace_vars(p_sentence text, p_vars jsonb)
 returns text
as
$$
declare
  l_rec record;
  l_result text;
begin
  l_result := p_sentence;
  for l_rec in select * from jsonb_each_text(jsonb_strip_nulls(p_vars)) as x(var,value) 
  loop
     l_result := replace(l_result, l_rec.var, l_rec.value);
  end loop;
  return l_result;
end;
$$
language plpgsql;

Then you can use it like this:

select s.id, s.sentence, replace_vars(s.sentence, to_jsonb(l)) new_sentence
from sentences s
  left join logs l on l.sentenceid = s.id;

Online example

Upvotes: 2

Related Questions