Solved

Locate function in db2

Posted on 2010-08-31
12
3,249 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
  • 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 45

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 18

Expert Comment

by:daveslash
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 45

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 45

Accepted Solution

by:
Kent Olsen earned 500 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 45

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 (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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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