T-SQL Inserting field values into match variable values

Question

I know how to concatenate several variables together in a single expression but I am trying to re-assemble the text of an event the software does from the SQL database.

The key is the text is variably positioned and variables are not always present and there can be up to 10.

For example I have a field like the following appearing in a field.

%1 has disconnected from %2 at %3 on entity %4.

I then go to another table to lookup the values in that remote table and throw them into separate fields and get output that looks like

Field 1: %1 has disconnected from %2 at %3 on entity %4.
Field 2 (%1): Station1
Field 3 (%2): Server237
Field 4 (%3): 2017-09-23 23:15:00
Field 5 (%4): LSAN237

What I want to know how do do is assemble that in a single field such as the following with the variability of the text.

"Station 1 has disconnected from Server237 at 2017-09-23 23:15:00 on entity LSAN237".

The text of field 1 is HIGHLY variable (over 50k unique string combinations) and there are up to 10 variables contained within each message.

I am not sure its relevant but the code is fairly long just to get the variables matched up to the events but it is basically me pulling the event log with a with clause and then selecting the section of log and then doing a series of joins and sub-queries for each variable. It is just the re-assembly of the original message to the vendors message to match the software is the challenge.

I am using SQL 2008 R2.

Gordon Linoff · Accepted Answer · 2017-09-24 15:09:03Z

2

Do you just want replace():

select replace(replace(replace(replace(field1, '%1', field2
                                      ), '%2', field3
                              ), '%3', field4
                      ), '%4', field5
               ) as with_replacements
. . .

When I use replace() with wildcards, I usually use [1] rather than %1, because the format cannot be confused with %10.

answered Sep 24, 2017 at 15:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

CRSouser Over a year ago

Hopefully final comment, the straight replace blanks out the entire output for me if the param field is null but works exactly as expected if all parameters are there. A single param though being null or blank results in no output.. any tips on working around this?

Gordon Linoff Over a year ago

@CRSouser . . . Use coalesce(). Instead of '%1', field2, use '%1', coalesce(field2, ''). Of course, you need to be sure that a blank is acceptable for what you want to do.

Collectives™ on Stack Overflow

T-SQL Inserting field values into match variable values

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