• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4588
  • Last Modified:

Locate function in db2

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
mohitgyl
Asked:
mohitgyl
  • 4
  • 3
  • 2
  • +2
1 Solution
 
momi_sabagCommented:
you would expect db2 to have a reverse function but it does not
you may want to implement one yourself
0
 
mohitgylAuthor Commented:
Ok...Can you suggest something else for this issue.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
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.

 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
tliottaCommented:
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
 
mohitgylAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
so if you want to take the last one, just use the reverse function that presented here by dave
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
mohitgylAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now