Solved

Locate function in db2

Posted on 2010-08-31
12
2,972 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
Comment Utility
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
Comment Utility
Ok...Can you suggest something else for this issue.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mohitgyl
Comment Utility
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:Kdo
Comment Utility
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
Comment Utility
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:
Kdo earned 500 total points
Comment Utility
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:Kdo
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now