convert string to numeric 1

[atarrqis]

CR11.5 and Oralce
I have a field with strings, generally like 105.2.12, 105.2.2, 105.D.5, 105.F1.ABC
I'd like these to sort nicely, in particular, 105.2.2 should come before 105.2.12 but since it is a string it does not.
Please help.

 

[SkipVought]

hi,

If you want 105.2.2 to sort before 105.2.12, then 105.2.2 ought to be 105.2.[highlight #8AE234]0[/highlight]2



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[atarrqis]

But I can't control if the data is numbers or alpha so I don't know how to correctly add a zero when needed.
I also can't control how many characters are in each section so I wouldn't know how many leading zeros to add.

 

[SkipVought]

well you would need to 1) accumulate ALL the numbering string and 2) deconstruct (parse) the strings, 3) using the max string length in each section of the parsing, pad with ZEROS to that length and finally 4) reconstruct the strings.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Didn't have time to test the code, but assuming there is always 3 'components' to the string. they are always separated by periods, and each of the components is never longer than 4 characters, the following approach should provide a result on which you could base the sort:

[Code {@SortOrder}]
Right('0000' + Split({Table.Field},'.')[1],4) + Right('0000' + Split({Table.Field},'.')[2],4) + Right('0000' + Split({Table.Field},'.')[3],4)
[/Code]

Hope it helps

Cheers
Pete

 

[atarrqis]

That's very helpful, Pete. Just one thing, most of the time we only have 3 components but occaisionally 4. I tried adding a 4ths section but I get the error "A sbuscript must be between 1 and the size of the array". Since this is rare, I'd live with these exceptions showing up in whatever order the system wants to treat them. So I need some code that looks at the size of the array, uses the above when = 3, and just uses the full string otherwise. Do you know how to check the size of the array?

 

[pmax9999]

Try:

If      Ubound(Split({Table.Field},'.')) = 4
Then    Right('0000' + Split({Table.Field},'.')[1],4) + Right('0000' + Split({Table.Field},'.')[2],4) + Right('0000' + Split({Table.Field},'.')[3],4) + Right('0000' + Split({Table.Field},'.')[4],4)
Else
If      Ubound(Split({Table.Field},'.')) = 3
Then    Right('0000' + Split({Table.Field},'.')[1],4) + Right('0000' + Split({Table.Field},'.')[2],4) + Right('0000' + Split({Table.Field},'.')[3],4)
Else    {Table.Field}

Cheers
Pete

 

[SkipVought]

Can't you use the [highlight #AD7FA8]Ubound(Split({Table.Field},'.'))[/highlight] as the limit for a loop instead?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Yep you could use a loop but as the OP stated there was only ever 3 or 4 components to it, didn't see the point.

 

[SkipVought]

The point is that [highlight #FCE94F]in general[/highlight] such a solution could be applied, not matter how many indents. I make it a practice to try to compose [highlight #FCE94F]general solutions[/highlight], especially if the difference in effort is negligable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Then Skip, perhaps you could have provided the solution in your first post.

 

[SkipVought]

I'm not a CR programmer, but I thought that your solution using the Split function was a great solution and I should have given you a star at that time. Just a thought to make it even better, IMHO.

If I were a CR coder, I would post such a solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Thanks Skip. I do appreciate the star.

Suggesting a solution with a more general application is reasonable. The following code will do it:

WhilePrintingRecords;

Local NumberVar S := UBound(Split({Table.Field},'.'));     // Determine number of sections
Local NumberVar i;                                         // Loop counter
Local StringVar O;                                         // Output string

For i := 1 to S Do
O := O + Right(('0000' + Split({Table.Field},'.')[i]),4);

O

The other limiting factor in my solution is the maximum number of characters in any of the original string "components" (ie the string parts between the periods). My code allows for just 4 but to make the code more general it would need to determine the maximum length of any of the components in every record so as to be able to "pad" the results with the correct number of leading zeros. While this is possible, it would make the report much less efficient as it would require a full pass of the dataset before processing the actual report. in my experience, sometimes it is just more practical (not necessarily ideal) to code for the worst known/anticipated situation and deal with future changes if/when they occur.

Cheers

Pete

 

[atarrqis]

I appreciate all your input but I should have stated that I group data on this string so I don't think I can use the "WhilePrintingRecords".

 

[pmax9999]

Did you try the the earlier solution (11 Apr 14 4:31)? It did not use variables and so avoids Print Time formulas.

Cheers
Pete

 

[atarrqis]

Yes, that post worked fine for my needs. Thanks to everyone!