Solved

how to get the substring vaule in db2

Posted on 2014-12-20
11
295 Views
Last Modified: 2015-01-05
i have  table called xyz in that there is a column called plan
plan column have the values like
HSPPSO-HEALTHSOLUTIONS-PPSO
HSBBMS-HEALTHSOLUTIONS-BBMS
HSBPO-HEALTHSOLUTIONS-BPO

IN THE ABOVE VALUES I NEED TO FETCH THE VALUE ONLY FROM FIRST HYPHEN(-)TO END OF THE STRING
Example:  "HEALTHSOLUTIONS-PPSO"

HOW TO GET THE VALUE USING SUBSTRING
I AM USING DB2(DATABASE)

CAN ANY ONE SUGGEST ME HOW TO DO
0
Comment
Question by:srikotesh
  • 5
  • 5
11 Comments
 
LVL 19

Expert Comment

by:mrwad99
Comment Utility
Try the following, it uses POSITION to find the first '-', then SUBSTR to extract everything from that up to the end of the string

SET :LOCATION = POSITION('-', 'HSPPSO-HEALTHSOLUTIONS-PPSO')
SET :RESULT = SUBSTR('HSPPSO-HEALTHSOLUTIONS-PPSO', RESULT)
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi Sri,

Use the locate function.  And you should probably include a sanity check to make sure that the string contains the '-' as a separator.  DB2 has a 2-parameter form of the substring function that is perfect for this application.  You only need pass the starting position and the function will return all of the characters to the end of the string.

SELECT case when (LOCATE ('-', columnname)) = 0 THEN '*not found*' 
       else substring (columnname, locate ('-', columnname) + 1) 
       end
FROM mytable;

Open in new window



Good Luck!
Kent
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
HI KENT,

BY using the above query i got the result
but a few plans the above query didnt worked properly
the below are the plans i am unable to fetch the result with the above query.
can u sugeest how to get all the plans

DPKL01NATIMTO - BlockCare Dental 4 Kids 1B
BH3551BAVIILP - Block Precision Bronze HMO<sup>SM</sup> 003
1MT2 - SelectTemp PPO (1-6 month coverage)
SPD843PPOIMTP - Block Cracks Block Shed Solution<sup>SM</sup> 4, a Multi-State Plan
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Is the problem with those extractions just a leading space?

If so, ltrim should solve it:

SELECT case when (LOCATE ('-', columnname)) = 0 THEN '*not found*' 
       else ltrim (substring (columnname, locate ('-', columnname) + 1) )
       end
FROM mytable;

Open in new window


Kent
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
hi kent

i tried with ltrim but still i got the issue with those plans
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
What do you need to extract from those descriptions?  And what are you getting?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
plan column is the combination of plan code + plan name

for example:
DPKL01NATIMTO(plan code) - BlockCare Dental 4 Kids 1B(plan name)

from that column i need only plan names because of that i am splitting to get the plan name
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Ok.  Then is it correct that these 4 lines should be returned unchanged?

WITH myfunc (plancode, planname)
AS
(
  SELECT 
         case when (LOCATE ('-', columnname)) = 0 THEN ' ' 
         else left (columnname, locate ('-', columnname) - 1) 
         end as plancode,
         case when (LOCATE ('-', columnname)) = 0 THEN '*not found*' 
         else ltrim (substring (columnname, locate ('-', columnname) + 1) )
         end as planname
  FROM mytable
)
SELECT case when locate ('-', planname) == 0 then plancode || ' - ' planname else planname end
FROM myfunc;

Open in new window

0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
Hi kent,

sorry for the delay

select plan from xyz
if i use the above query some of plans are not coming
The below are the plans not coming from the query result.

DPKL01NATIMTO - BlockCare Dental 4 Kids 1B
BH3551BAVIILP - Block Precision Bronze HMO<sup>SM</sup> 003
1MT2 - SelectTemp PPO (1-6 month coverage)
SPD843PPOIMTP - Block Cracks Block Shed Solution<sup>SM</sup> 4, a Multi-State Plan

if i use the query like below all the plans are coming

select * from xyz;

My question is where is the problem strucking to get the all plans
due to this reason only
whatever u suggested that query fetching the all plans except i mention in the above plans
if i got all the plans then only it will be useful to me
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi srikotesh,

You've got a data issue that is presenting some problems.  That's a common side-effect of putting too much information into a single column.

WITH mytable (columnname)
AS
(
  SELECT 'HSPPSO-HEALTHSOLUTIONS-PPSO' from sysibm.sysdummy1
  UNION ALL
  SELECT 'HSBBMS-HEALTHSOLUTIONS-BBMS' from sysibm.sysdummy1
  UNION ALL
  SELECT 'HSBPO-HEALTHSOLUTIONS-BPO' from sysibm.sysdummy1
  UNION ALL
  SELECT 'DPKL01NATIMTO - BlockCare Dental 4 Kids 1B' from sysibm.sysdummy1
  UNION ALL
  SELECT '1MT2 - SelectTemp PPO (1-6 month coverage)' from sysibm.sysdummy1
),
myfunc (plancode, planname)
AS
(
  SELECT 
         case when (LOCATE ('-', columnname)) = 0 THEN ' ' 
         else left (columnname, locate ('-', columnname) - 1) 
         end as plancode,
         case when (LOCATE ('-', columnname)) = 0 THEN '*not found*' 
         else ltrim (substr (columnname, locate ('-', columnname) + 1) )
         end as planname
  FROM mytable
)
SELECT case when locate ('-', planname) = 0 then plancode || ' - ' || planname else planname end
FROM myfunc;

Open in new window



That query uses a sampling of the data that you provided.  The results are:
DPKL01NATIMTO  - BlockCare Dental 4 Kids 1B
HEALTHSOLUTIONS-BPO
HEALTHSOLUTIONS-BBMS
HEALTHSOLUTIONS-PPSO
SelectTemp PPO (1-6 month coverage)

Open in new window


Does that look like what you're expecting?  If not, what needs to be changed?

Kent
0
 
LVL 1

Author Closing Comment

by:srikotesh
Comment Utility
Thanks for ur suggestions.i got the result with the help of above query as u mentioned.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now