?
Solved

Locate function in db2

Posted on 2010-08-31
12
Medium Priority
?
3,773 Views
Last Modified: 2012-05-10
I am locating the position of some character say 'p' as

select locate('p','purpose') from test;

By default it locates from left to right or starting of the string to end of the string. Can i locate it in an opposite way i.e from right to left or end of the string to beginning of the string.
0
Comment
Question by:mohitgyl
[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
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33568650
you would expect db2 to have a reverse function but it does not
you may want to implement one yourself
0
 

Author Comment

by:mohitgyl
ID: 33568835
Ok...Can you suggest something else for this issue.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33569114
Hi mohitqul,

Since DB2 doesn't have a built-in function to find anything but the first occurrence of a specific character or substring, you'll need to write your own function or use recursive SQL.

The SQL below will scan the strings in a table and return the position of all of the spaces in the string.


Only a slight change should make it work for you.



Kent

WITH rquery (snum, wordnum, position, remainder)
AS
(
  SELECT base.snum, 1, 
    locate (' ', mystring) position, 
    case when locate (' ', mystring) > 0 then
      substr (mystring, locate (' ', mystring) + 1)
    else
      NULL
    end remainder
  FROM mystrings base 
  
  UNION ALL
  
  SELECT snum, wordnum + 1, 
    case when locate (' ', remainder) > 0 then 
      position + locate (' ', remainder)
    else
      0
    end position,
    case when locate (' ', remainder) > 0 then
      substr (remainder, locate (' ', remainder) + 1)
    else
      NULL
    end remainder
  FROM rquery T0
  WHERE remainder is not NULL
)
SELECT * 
FROM rquery rq
order by 1, 2

Open in new window

0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 18

Expert Comment

by:Dave Ford
ID: 33569295

The attached REVERSE function should help.

Alternately, you'd write a function that would accomplish what you want.

HTH,
DaveSlash

SQL-Function---Reverse.txt
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33580318
Can i locate it in an opposite way...

Do you need a reversal? Or do you simply need to locate the last occurrence? A reversal would supply a string that you could use LOCATE() over (and then you'd need to calculate the reverse position number, which gets interesting for VARCHAR columns), but a proc that simply returned the position of the occurrence that you wanted might be better.

What platform are you running DB2 on, and what version of DB2 is this? On some platforms (all platforms?), this might actually be better done with an external proc.

Tom
0
 

Author Comment

by:mohitgyl
ID: 33584325
In  the string there can be any number of 'p'. So, instead of counting the location from starting point which will first tell the location of first 'p' then second and then third. So if there are three 'p' in the string, if start locating from end side, i need to locate only the third 'p' but not first and second which i dont need.

Further, I am using it on Windows Server 2003 and the version is Enterprise Server Edition V9.5.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33586198
Hi mohitqyl,

DB2 has 3 basic products.  DB2 for Z/OS, AS400, and UDB/LUW.  You're running a very recent version of UDB/LUW so the host O/S shouldn't make much difference.  (The SQL would be no different if you were running on Linux or Unix.)

About how many rows do you have to process?


Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33586586
so if you want to take the last one, just use the reverse function that presented here by dave
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1500 total points
ID: 33587033
Well,

Since you don't know the number of 'p's in the string, you need to walk through the string until you find the third one.  It's possible in traditional SQL, but ugly.  A UDF is probably in order to do this.  The other viable solution is recursive SQL.

Both are shown below.  Note that the traditional SQL finds just the 2nd 'p'.  Going one deeper gets a lot uglier.


Kent

create table tst
(
  id    integer not null generated by default as IDENTITY,
  list  varchar (400)
);


DELETE FROM tst;
INSERT INTO tst (list) VALUES ('please pass the pate to pete for passover');

--  Solve with recursive SQL

WITH query1 (snum, wordnum, rpos, remainder)
AS
(
  SELECT base.id, 1, 
    case when locate ('p', list) > 0 then 
      locate ('p', list)
    else
      0
    end rpos,
    list
  FROM tst base 
  
  UNION ALL
  
  SELECT snum, wordnum + 1, 
    case when locate ('p', remainder, rpos+1) > 0 then 
      locate ('p', remainder, rpos+1)
    else
      0
    end rpos,
    remainder
  FROM query1 T0
  WHERE rpos > 0 
)
SELECT case when rpos > 0 then substr (list, rpos) else '' end 
FROM query1, tst
where query1.snum = tst.id
  and wordnum = 3
union all
select list from tst;

-- solve with traditional SQL (no UDF)

SELECT
  case when locate ('p', list) > 0 then
       case when locate ('p', substr (list, locate ('p', list))) > 0 then
            substr (list, locate ('p', substr (list, locate ('p', list)+1)))
            else ''
       end
       else ''
  end
FROM tst;

Open in new window

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33769039
This item belongs in the PAQ.  The topic comes up from time to time.

I'm glad that the author was able to solve the problem, apparently on his own, but there were two workable solutions and a couple more good comments to his question.
0
 

Author Closing Comment

by:mohitgyl
ID: 33822307
Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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
Course of the Month10 days, 8 hours left to enroll

765 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