asked on

DB2 SQL

I have SQL column which holds the data as shown below

TYPE=A,CREATE USER=KWINTRODE ,MODIFIED USER=KWINTRODE ,2009 COUNTY TAXES

TYPE=N,CREATE USER=ZEKE ,MODIFIED USER=ZEKE ,ACH PAR NUMBER: 011280926

TYPE=C,CREATE USER=JWICK ,MODIFIED USER=JWICK ,ORECM-ELDER OHIO DELAWARE BUS TRUST LOAN 19700218

TYPE=A,CREATE USER=AMOORE ,MODIFIED USER=AMOORE ,Electric 2/7/11-3/7/11 & late fee
I want to write SQL to show the information below from the above data(bolded text):

A - 2009 COUNTY TAXES
N - ACH PAR NUMBER: 011280926
C - ORECM-ELDER OHIO DELAWARE BUS TRUST LOAN 19700218
A - Electric 2/7/11-3/7/11 & late fee

momi_sabag

select substring(your_column,6,1) || ' - ' || substring(your_column, LOCATE_IN_STRING(your_column, ',', 1, 2) + 1, 99999)
from your_table

Mani Pazhana

ASKER

Thanks

Here is my SQL:

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE_IN_STRING(OBJECT, ',', 1, 2) + 1, 99999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

i am getting this error:

SQL0204N "*LIBL.LOCATE_IN_STRING" is an undefined name. SQLSTATE=42704

Mani Pazhana

ASKER

i am using DB2 version 8.0

stalhw

I think LOCATE_IN_STRING came with v9.
Before that you have to use LOCATE, but locate only finds you the first occurence... so you'll need to use it twice....
so instead of LOCATE IN STRING:
LOCATE(',',SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,99999))
So you end up with:
Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,99999)) + 1, 99999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

hopefully this will work

Mani Pazhana

ASKER

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,99999)) + 1, 99999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

when i tried, i got this error:

SQL0138N The second or third argument of the SUBSTR function is out of range. SQLSTATE=22011

Mani Pazhana

ASKER

i could run the SQL:

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,9999)) + 1, 9999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

now it is returning the result:

C - ,MODIFIED USER=SMIRABELLA ,Account # 01309
C - ,MODIFIED USER=SMIRABELLA ,Account # 54194-20
A - ,MODIFIED USER=ZEKE , 610376179027820081025

i want my output like shown below:

C - Account # 01309
C - Account # 54194-20
A - 610376179027820081025

SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mani Pazhana

ASKER

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',SUBSTRING(OBJECT, LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1,9999)) + 1, 9999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

still the same result:

C - ,MODIFIED USER=SMIRABELLA ,Account # 01309
C - ,MODIFIED USER=SMIRABELLA ,Account # 54194-20
A - ,MODIFIED USER=ZEKE , 610376179027820081025
A - ,MODIFIED USER=ZEKE , 717 761-5261 072 07Y20081025
A - ,MODIFIED USER=ZEKE , 717840109455520081025

stalhw

Oh right, my error there's 3 ","
so last suggestion from momi_sabaq should work, he just added one more LOCATE to get the last ","

Mani Pazhana

ASKER

please give me the SQL

stalhw

Ok adding one more substring & locate gives:

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',substring(OBJECT, LOCATE(',', SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,9999)) + 1, 9999)) +1, 9999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

Mani Pazhana

ASKER

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',substring(OBJECT, LOCATE(',', SUBSTRING(OBJECT, LOCATE(',',OBJECT)+1,9999)) + 1, 9999)) +1, 9999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

it brings everything:

C - CREATE USER=SMIRABELLA ,MODIFIED USER=SMIRABELLA ,Account # 01309

C - CREATE USER=SMIRABELLA ,MODIFIED USER=SMIRABELLA ,Account # 54194-20

A - CREATE USER=ZEKE ,MODIFIED USER=ZEKE , 610376179027820081025

ASKER CERTIFIED SOLUTION

stalhw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mani Pazhana

ASKER

Excellent. Thank you very much.

Select substring(OBJECT,6,1) || ' - ' || substring(OBJECT, LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) +1, 9999)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

Results:

C - Account # 01309
C - Account # 54194-20
A - 610376179027820081025
A - 717 761-5261 072 07Y20081025
A - 717840109455520081025
A - 319234884820081028
A - 319396221120081028
A - 319753549120081028

Mani Pazhana

ASKER

Thanks to both of you.

stalhw

I don't understand why the query doesn't work, it should.. unless what looks like a comma is not a comma?
and for testing run:

Select LOCATE(',',OBJECT,LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1)+1) ,
LOCATE(',',OBJECT, LOCATE(',',OBJECT)+1),
LOCATE(',',OBJECT)
from LAWQA.L_HAPI
WHERE OBJECT IS not null

just to make sure each locate identifies correctly the location of each comma

stalhw

ok, so without the many substrings it works, great.

Mani Pazhana

ASKER

please take a look at it if you have time.

https://www.experts-exchange.com/questions/27416191/DB2-Sql.html

stalhw

Sadly I'm not that familiar with DB2, and I don't have access to one to do testing, so can't really help with that other question.