Go Premium for a chance to win a PS4. Enter to Win

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

how to get the substring vaule in db2

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
srikotesh
Asked:
srikotesh
  • 5
  • 5
1 Solution
 
mrwad99Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
srikoteshAuthor Commented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
srikoteshAuthor Commented:
hi kent

i tried with ltrim but still i got the issue with those plans
0
 
Kent OlsenData Warehouse Architect / DBACommented:
What do you need to extract from those descriptions?  And what are you getting?
0
 
srikoteshAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
srikoteshAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
srikoteshAuthor Commented:
Thanks for ur suggestions.i got the result with the help of above query as u mentioned.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now