Concatenate 2 fields in 1 field with condition

[gabydrdoom]

Helo,

I have a table with two fields named map1 and map2 and I want to make concatenation according to the following model:
map1 map2 map12
100001 1 100001001
100001 22 100001022
100001 87 100001087
100002 999 10000299
I want to concatenate like this:
for map2=1: map12=map1+'0'+'0'+map2
for map2=22: map12=map1+'0'+map2
for map3=999: map12=map1+map2

 

[Chris Miller]

I don't have looked into your attachment. What is it? Why is it there?
What you can use in VFP8/9 is ICASE. I wonder about your requirements being too specific. For one thing, if mape is 1, then map1+'0'+'0'+map2 is always the same as map1+'002'.
I think your requirement is only about the length fo map2, which means you'd just pad map2 with leading zeros and then add it to map1.

For that you'd use ALLTRIM(map1)+PADL(ALLTRIM(map2),3,'0')

Another simple idea, if map1 is char(6), map2 is char(3) and map12 should become char(9), that means you'd just need to have map2 right aligned and then replace all spaces with 0 digits with a CHRTRAN().

 

[GriffMG]

hmm what about map2=87? i suppose the example follows that...

assume map1 and map2 are strings...

SELECT MYTABLE
SCAN
    DO CASE
        CASE MAP2 = "1"
            REPLACE MAP12 WITH MAP1+"00"+MAP2
        CASE MAP2 = "22"
            REPLACE MAP12 WITH MAP1+"0"+MAP2
        CASE MAP2 = "87"
            REPLACE MAP12 WITH MAP1+"0"+MAP2
        CASE MAP2 = "999"
            REPLACE MAP12 WITH MAP1+MAP2
    ENDCASE
ENDSCAN

or

SELECT MYTABLE
REPLACE ALL MAP12 WITH MAP1+RIGHT("00"+ALLTRIM(MAP2),3)

 

[GTGeek88]

In the final line, did you mean MAP3 or did you mean to type MAP2. You mentioned two fields and then a third field or variable shows up. I'm going to assume you meant MAP2. Also, is this line what you meant:

100002 999 10000299

There's some confusion here. Your data in the post and your data in the file seem to not match your algorithm.

It could be taken that your solution is a simple concatenation as GriffMG indicated, though I'd do this:

alltrim(map1) + padl(alltrim(map2), 3, "0")

(alltrim needed since these are fields)

 

[gabydrdoom]

hmm what about map2=87? i suppose the example follows that...

assume map1 and map2 are strings...

SELECT MYTABLE
SCAN
    DO CASE
        CASE MAP2 = "1"
            REPLACE MAP12 WITH MAP1+"00"+MAP2
        CASE MAP2 = "22"
            REPLACE MAP12 WITH MAP1+"0"+MAP2
        CASE MAP2 = "87"
            REPLACE MAP12 WITH MAP1+"0"+MAP2
        CASE MAP2 = "999"
            REPLACE MAP12 WITH MAP1+MAP2
    ENDCASE
ENDSCAN

or

SELECT MYTABLE
REPLACE ALL MAP12 WITH MAP1+RIGHT("00"+ALLTRIM(MAP2),3)

I gave only a few examples. They are all about a few hundred thousand.
Thank you

 

[Chris Miller]

Gaby,

now Griff, GTGeek, and I concluded the same solutions (almost) and you still don't see it, it seems.

SELECT map1, map2, CAST(ALLTRIM(map1)+PADL(ALLTRIM(map2),3,'0') as C(9)) as map12 from your.dbf

Look if that fits.

Edit: It can fail, In case map1 is ever longer than 6 digits or map2 is longer than 3 digits that'll not work out fine. But then come up with a more concise definition of your requirement, defining a requirement by examples is always telling you don't have an understanding of your problem more than that two fielkds have to be combined into one.

 

[Chris Miller]

As it doesn't seem to work for you, you could do us (and in turn yourself) a favor and analyze your data about NULL values and maximum lengths.

Can you tell us what you get from these four queries? Assuming your dbf is called map.dbf:

Select Count(*) from map.dbf where map1 is null

Select Count(*) from map.dbf where map2 is null

Select Max(Len(Alltrim(map1))) as maxlen1 from map.dbf

Select Max(Len(Alltrim(map2))) as maxlen2 from map.dbf

 

[Doug Lindauer]

You didn't specify the data types and lengths of the fields. It looks like they're character type but the code will be a little different depending. In any case you'll be using either padl() or chrtran() somewhere in there along with "replace all ...". Keep in mind when posting a question like this, to include all the pertinent information. And data type is almost always highly pertinent.

And Chris's point about null values and max lengths may also be necessary to know.

 

[GriffMG]

map3?