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

Looks like we have a winner here. The SQL does the following:

- If a period occurs after 'PAT', make no change.

- If the PAT string is not followed by a comma, convert the number to x.xx.

- If the PAT string is followed by a comma, convert the number to x.xx and copy the rest of the string

There are certainly other SQL queries that will solve this, but this one is fairly clean.

Kent

Open in new window