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.
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
Using the Conditional String Concatenation syntax this can be simplified to this code
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.
First in case you have not built a URL with variables before, a little information, when you pass variables the format is:
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:
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
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.
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.
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.
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
4 thoughts on “Conditional String Concatenation”
Thanks for your posts/videos
sSelect = “select * from table where 1=1 ”
if sField1″” then sSelect += ” and Field1=” + sField1
if sField2″” then sSelect += ” and Field2=” + sField2
Nice little hint. Thanks Pete. Hope you I hope to contact you soon. (reg. LabDriver) #pragma
Buenas Noches. No se pueden ver(cargar) , las imágenes explicativas .
Sorry this is a very old blog post and when we transferred the blog a few years ago some of the images for the older articles were lost and not recoverable.