Solved

# Need SQL script to manipulate a substring to contain decimal point. Can anyone help with this?

Posted on 2015-01-05

Dear SQL gurus

I am looking to come up with a simple SQL that will scan DB2 records(rows) for some character string that begins with "PAT" followed by one or more numbers. Upon finding the character string

and numbers, the sql should a decimal point at exactly 2 decimal position from the right just as if one writing 20.00. The decimal point remains the same regardles of the size of the numbers.

The format of the record looks as follows:

Current:

Current:

LGG20140115092300,SNO0388,CUP20316700000,DNO20,QTY1,AMT1732,PAT1025,CUP85162600203 <<<___ See "PAT" + numbers

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT17156,CUP3151

LGG20140108150500,SNO0388,CUP20476000000,DNO30,QTY1,AMT90114,PAT152736,CUP3199 <<<<___ See "PAT" + numbers

LGG20140108150500,SNO0388,CUP20476000000,DNO30,QTY1,AMT90114,PAT173,CUP1099

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT052,CUP0051

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT52,CUP0151 <<<___ See "PAT" + numbers

The result of what I am looking for should look like the folllowing:

Current:

LGG20140115092300,SNO0388,CUP20316700000,DNO20,QTY1,AMT1732,PAT10.25,CUP85162600203

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT171.56,CUP3151 <<<___ See "PAT" + numbers(large numbers)

LGG20140108150500,SNO0388,CUP20476000000,DNO30,QTY1,AMT90114,PAT1527.36,CUP3199 <<<___ See "PAT" + numbers(large numbers)

LGG20140108150500,SNO0388,CUP20476000000,DNO30,QTY1,AMT90114,PAT1.73,CUP1099

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT0.52,CUP0051

LGG20140113094400,SNO0388,CUP20476000000,DNO30,QTY1,AMT10122,PAT.52,CUP0151

Can someone help rovide a SQL solution? Any help in resolving request will be much appreciated.

Thanks

Enyimba