eggwhites eggwhites - 23 days ago 9
SQL Question

How to Concat, replace, and stuff one column

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?

Answer

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