Solved

how to get the substring vaule in db2

Posted on 2014-12-20
11
302 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
ID: 40510837
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
ID: 40510962
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
ID: 40511415
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 45

Expert Comment

by:Kdo
ID: 40511550
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
ID: 40511578
hi kent

i tried with ltrim but still i got the issue with those plans
0
 
LVL 45

Expert Comment

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

Author Comment

by:srikotesh
ID: 40511894
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
ID: 40511905
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
ID: 40519719
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
ID: 40520550
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
ID: 40531727
Thanks for ur suggestions.i got the result with the help of above query as u mentioned.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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