SQL Use column value in another column

Question

What I'm trying to do is best described as mad-libs within SQL where there is one column that acts like a "template" that will end with all the values from the other columns inserted into the correct position.

An example of what a sample table might look like is below. The final query should return: 1. Yesterday, I saw 20 Angry Cats Bouncing 20 sweaters around the park. 2. It would be so Hilarious if Fish could Run while on a 7.5 year mission to the moon.

ID | Noun | Adjective | Verb     | Number | Sentence
===================================================
1  | Cat  | Angry     | Bouncing | 20     | Yesterday, I saw [Number] [Adjective] [Noun]s [Verb] [Number] sweaters around the park.
2  | Fish | Hilarious | Run      | 7.5    | It would be so [Adjective] if [Noun] could [Verb] while on a [Number] year mission to the moon.

This is a silly example, much like mad-libs are in real life. Nevertheless, it demonstrates what is trying to be accomplished.

What DBMS are you using? Edit the question and add the right tag. — sticky bit
– sticky bit, Commented Jan 8, 2020 at 23:23

Gordon Linoff · Accepted Answer · 2020-01-08 23:23:36Z

2

You would use replace() and replace() again:

select t.*,
       replace(replace(replace(replace(sentence, '[noun]', noun
                                      ), '[verb]', verb
                              ), '[Adjective]', Adjective
                      ), '[Number]', number
               ) as filled_in
from t;

answered Jan 8, 2020 at 23:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Isaac McDaniel Over a year ago

Cool! Now all I have to figure out is how to deal with any columns that are NULL

Gordon Linoff Over a year ago

You can use coalesce(), such as coalesce(verb, ''). If the number is actually stored as a number (and not a string), you'll need to handle that with a type conversion.

Collectives™ on Stack Overflow

SQL Use column value in another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related