So I have Two columns Id like concatenated together, have the dashes and the first two characters removed, as well as define the number of digits returned.
Po_no value is 18-29201-202
Line_no value is 6 (id like to return this as 006)
Id like to end up with this 29201202006
Right now I am combining them and removing the dashes with this:
concat(replace(oe_hdr.po_no, '-', ''), job_price_line.line_no) as [PO and LINe NO],
You want to concatenate two pieces basically, the modified PO and the Line in a standard format. You're really close already, but your formulas for both pieces are a little off.
replace(oe_hdr.po_no, '-', '') is correct, but you only want the right 8 digits assuming that your PO format is always consistent (if not, you'll need something more complex such as finding the first "-", and taking the
substr() depending on system). In short, you're actually looking for the following:
right(replace(oe_hdr.po_no, '-', ''),8)
On the second part, the simplest way to add leading 0s to a string is by concatenation of however many 0's could be needed followed by a
right() to the desired string length:
You can combine those two to get your full string:
concat( right(replace(oe_hdr.po_no, '-', ''),8), right(concat("000",job_price_line.line_no),3) )