How to Concat, replace, and stuff one column

Question

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.

Example

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],

With a result of:

18292012026

How could I also remove the first two digits of po_no and also ensure that the line_no is always three digits long?

Here is how you pad the 6 http://stackoverflow.com/a/16760967/215752 — Hogan, Nov 17 '16 at 16:40
is Po_no always the same number of characters? If so you can use `RIGHT()` — Hogan, Nov 17 '16 at 16:41

score 1 · Accepted Answer · answered Nov 17 '16 at 16:46

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 mid()/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:

Right(concat("000",job_price_line.line_no),3)

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)
    )

How to Concat, replace, and stuff one column

1 Answers1