Concatenate values

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Concatenate values

Postby cavillafuerte » Wed Oct 24, 2012 11:06 pm

I am trying to add a +column by concatenating the values from other columns but not having any luck. How can I concatenate the values?
Example:
I have a record that has the following columns and their values:
Column Value
Qsec NE
Sec 12
Twp 38
Rge 6
Mer 3

I want to add a column called LandDescription and give it a value of 'NE-12-38-6-3'
cavillafuerte
 
Posts: 32
Joined: Fri Dec 02, 2011 8:43 pm

Re: Concatenate values

Postby Bob Cergol » Wed Oct 24, 2012 11:26 pm

Assuming the columns are all Char or VarChar, and you don't have null values in any of them, then in MS-SQL this would work:

Column Name = LandDescription
Column Type = Text
Column Formula = [Qsec] + '-' + [Sec] + '-' + [Twp] + '-' + [Rge] + '-' + [Mer]

I don't often use the brackets, but in case 'Sec' is a reserved word in the backend I added them.

If the data source is a Dyn-join, and there are redundant column names, you'll find you need to qualify the names with the table alias name, for example: dai_.Qsec, j2.Twp, etc.

Null values might give errors so if you have them you might use:
isNull([Qsec],' ') + '-' + isNull([Sec],' ') + '-' + isNull([Twp],' ') + '-' + isNull([Rge],' ') + '-' + isNull([Mer],' ')

If any columns are non-numeric you will of course have cast or convert them to char, for example if 'Sec' is an integer then:
isNull([Qsec],' ') + '-' + cast(isNull([Sec],0) as varchar) + '-' + isNull([Twp],' ') + '-' + isNull([Rge],' ') + '-' + isNull([Mer],' ')

I don't often encounter nulls in the data application data I deal with, but if I do, I prefer to get rid of them in my dyn-View.

Regards,
Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm


Return to Technical Support

cron