sql - How to Concat, replace, and stuff one column -

so have 2 columns id concatenated together, have dashes , first 2 characters removed, define number of digits returned.

example

po_no value 18-29201-202

line_no value 6 (id return 006)

id end 29201202006

right combining them , removing dashes this:

concat(replace(oe_hdr.po_no, '-', ''), job_price_line.line_no) [po , line no], 

with result of:

18292012026 

how remove first 2 digits of po_no , ensure line_no 3 digits long?

you want concatenate 2 pieces basically, modified po , line in standard format. you're close already, formulas both pieces little off.

replace(oe_hdr.po_no, '-', '') correct, want right 8 digits assuming po format consistent (if not, you'll need more complex such finding first "-", , taking mid()/substr() depending on system). in short, you're looking following:

right(replace(oe_hdr.po_no, '-', ''),8)

on second part, simplest way add leading 0s string concatenation of many 0's needed followed right() desired string length:

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

you can combine 2 full string:

concat(     right(replace(oe_hdr.po_no, '-', ''),8),     right(concat("000",job_price_line.line_no),3)     )