eggwhites eggwhites - 1 year ago 68
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.


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:


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 Source

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:


You can combine those two to get your full string:

    right(replace(oe_hdr.po_no, '-', ''),8),
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download