Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

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

0
mani_sai
Asked:
mani_sai
  • 10
  • 7
  • 2
2 Solutions
 
momi_sabagCommented:
select substring(your_column,6,1) || ' - ' || substring(your_column, LOCATE_IN_STRING(your_column, ',', 1, 2) + 1, 99999)
from your_table
0
 
mani_saiAuthor Commented:
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
0
 
mani_saiAuthor Commented:
i am using DB2 version 8.0
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
stalhwCommented:
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
0
 
mani_saiAuthor Commented:

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
0
 
mani_saiAuthor Commented:
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
0
 
momi_sabagCommented:
try
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
0
 
mani_saiAuthor Commented:

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

0
 
stalhwCommented:
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 ","
0
 
mani_saiAuthor Commented:
please give me the SQL
0
 
stalhwCommented:
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

0
 
mani_saiAuthor Commented:
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

0
 
stalhwCommented:
but there has to be a simpler way, LOCATE can accept a third parameter so I was expection momi_sai solution to work...

the previous query shoudl work, but it's not optimal...
Some thing like this should also work and should be faster.
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

0
 
mani_saiAuthor Commented:
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


0
 
mani_saiAuthor Commented:
Thanks to both of you.
0
 
stalhwCommented:
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
0
 
stalhwCommented:
ok, so without the many substrings it works, great.
0
 
mani_saiAuthor Commented:
0
 
stalhwCommented:
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.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now