Conditional String Concatenation

973920808_1e924a8fce
Photo by nasrulekram

This week’s Uncle Pete’s corner will be covering Conditional String Concatenation. This is one of those handy little tools to toss in your toolbox and pull out once in a while when you have a need for it.

So what is conditional string concatenation? Well buried about half way done the online help page on string operators  is a section covering the [ and ] operators which is used optionally concatenate strings. There are some examples given but it took many several times of reading this section before I fully understood how to use them. So this article will give some practical examples using conditional string concatenation.

Creating a Where clause

I often have code that builds an SQL where clause based on several optional criteria. For instance take the following screen.

2013-02-14_2214

Where you want to build a where clause based on the checked conditions such as:

where field = Condition2 and field = Condition3

To accomplish this and make sure that the where clause is properly formatted, you might write code similar to this

2013-02-14_2224

Using the Conditional String Concatenation syntax this can be simplified to this code

2013-02-14_2226

It may only be 9 less lines of code, but that is nine less opportunities to introduce a bug!

Building a URL with variables

Another example where this comes in handy is creating a URL with a number of variables. Consider this screen.

2013-02-14_2250

First in case you have not built a URL with variables before, a little information, when you pass variables the format is:

http://mydomain.com?variable1=value1&variable2=value2

Notice there is a question mark before the first variable, and then the rest of the variables of delimited by ampersands. The code behind the button is:

2013-02-14_2254

Notice there are two uses of Conditional Concatenation being used here. The first one is used during the loop to place an ampersand between the variables after the first variable. And the second use places a question mark after the domain only if there are variables supplied.

Building a SQL IN Clause

Another need I often have is building an in clause for SQL, such as:

select * from table where id in(1,2,6,3)

So consider this screen

2013-02-14_2316

In this screen you can enter IDs into the table on the left, and the correct in clause will be built, this includes having no where clause at all if not IDs are entered into the table.

2013-02-14_2318  2013-02-14_2319

Traditionally the code to build this statement would include several IF statements. However if you look at the code behind the button, you will find that there is actually no IF statements at all.

2013-02-14_2319_001

There are three uses of conditional concatenation in the code. In the For Each loop a comma separates the IDs. After the Loop a close parentheses is included only if at least 1 ID was included. And finally the Where portion of the statement is only include if there were IDs included. As you can see this is a tremendous reduction in the amount of code that would normally be used.

Summary

So hopefully you now have an additional tool in your toolbox that will come in handy one day. I believe what separates productive developers from mediocore developers is have a nice toolbox full of handy little tools like this one at their disposal.

Be sure to go over to wxLive.us and watch the Uncle Pete’s corner, webinar that is the companion to this article.

Uncle Pete’s Corner is weekly webinar on all things WX every Friday 7:15 AM CST (5:15 PST), to watch the recorded version of this webinar, many other WX related webinars or to watch future ones live go to wxLive.us

[suffusion-the-author display=’author’]

Pete Halsted[suffusion-the-author display=’description’]

 

2 thoughts on “Conditional String Concatenation

  1. Thanks for your posts/videos

    Another one:

    sSelect = “select * from table where 1=1 ”
    if sField1″” then sSelect += ” and Field1=” + sField1
    if sField2″” then sSelect += ” and Field2=” + sField2

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s