Solved

how to get the substring vaule in db2

Posted on 2014-12-20
11
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Kent Olsen 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 2

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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 45

Expert Comment

by:Kent Olsen
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 2

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:Kent Olsen
ID: 40511892
What do you need to extract from those descriptions?  And what are you getting?
0
 
LVL 2

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:Kent Olsen
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 2

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:Kent Olsen
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 2

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 (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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

624 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