Leading Zero BAQ Calculated Field

We have a field that results in some with just numbers, some with both numbers and letters, and some with just letters. We want to add a 0 in front of the value of the field that just have numbers. What would a calculated field for this look like?

Is it supposed to match a certain length? Or are you just added a 0 to anything that’s a number?

add a 0 to anything that’s a number

Try this

CASE WHEN isnull( TRY_PARSE( table.Field AS decimal),0) = 0  THEN table.Field
ELSE CONCAT('0',table.Field)
end

Yeah I got an error. “Possible SQL injection”

Sorry, I had the select in there because I was testing in SSMS. You don’t need that.

This might be right now. I need to play around with my Group By, that’s where the error is right now

Here, this one is better. I was trying to use = null and it wasn’t working which is why I did the isnull(). If You need to use is null you don’t need to convert a null to 0.

1 Like

That did it! Thank you

I guess another scenario. How would this calculation change if I wanted it to also include the ones that have both number and text? So just number would be 100 which would change to 0100. Number and text would be 100 LR which would change to 0100 LR. This would only need to happen if initially there are 3 digits, if 4 or greater then do not add a 0

SUBSTRING((CONCAT('0000',table.Field)),LEN(CONCAT('0000',table.field))-3,4)

This is removing the LR in “100 LR”. I want it to become “0100 LR”

This is going to give you a specific number of characters and add 0’s if there isn’t enough. Play with the formula until you get what you want.

Other than that, You’re trying to get me to fly blind here, I don’t know what your data looks like, but it’s all basic string manipulation, so googling some stuff should get you a long ways.

1 Like

The data could be variations of: 100, 100 LR, LOBBY. If the value is 1000, it should keep it 1000. Or if 1000 LR, keep it 1000 LR. If the numbers are 3 digits, then add a 0 regardless of how many letters are behind it

You could try this.

Does Epicor support REGEXP?

Not sure. Try it out and let me know.

Error, REGEXP was not supported

The cheesy way to zero fill is to prepend ‘0000’ (or whatever the desired length of your string) to your value and then take the right four characters.

How would I prepend ‘0000’ to the value and take the right four characters?