Advanced Search

Find token into list of values

/ Published in: PL/SQL
Save to your folder(s)
Find a key from a list of values​​.
parameters:
sbValues​​: List of Values 'A;B;C;D'
sbToken: Key 'B'
sbSeparator: ',' or any defined sbValues

Example:
select FSBFINDTOKEN('A;B;C;D','B',';')
from DUAL;

-------------------
Y

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE OR REPLACE
  2. FUNCTION fsbFindToken(sbValues    IN VARCHAR2, 
  3.                       sbToken     IN VARCHAR2,
  4.                       sbSeparator IN VARCHAR2)
  5. RETURN VARCHAR2
  6. /*
  7.  *
  8.  * Function    : fsbFindToken.sql
  9.  * Autor       : ceduard0
  10.  * Fecha       : 16/02/2011
  11. **/
  12. IS
  13.  sbLocated VARCHAR2(1) := 'N';
  14.  sbListOfValues VARCHAR2(2000);
  15.  nuLength  NUMBER;
  16.  sbValue     VARCHAR2(2000);
  17. BEGIN
  18.   sbListOfValues := TRIM(sbValues);
  19.   nuLength       := LENGTH(sbListOfValues);
  20.  
  21.   IF (nuLength = LENGTH(REPLACE(sbListOfValues, sbSeparator))) THEN
  22.        IF (sbToken = sbListOfValues) THEN
  23.          sbLocated := 'Y';
  24.        END IF;--if (sbToken = sbListOfValues) then
  25.      ELSE
  26.         --Get data from list of value
  27.         FOR i IN 1..LENGTH(sbListOfValues) + 1 LOOP
  28.  
  29.            IF (TRIM(SUBSTR(sbListOfValues, i, 1)) <> TRIM(sbSeparator)) THEN
  30.                sbValue := sbValue || SUBSTR(sbListOfValues, i, 1);
  31.            ELSE
  32.               -- tbcausal(i).nuCausa:=to_number(sbValue);
  33.                IF (sbToken = sbValue) THEN
  34.                   sbLocated := 'Y';
  35.                   EXIT;
  36.                END IF;--if (sbToken = sbValue) then
  37.                sbValue := NULL;
  38.            END IF;--if (trim(substr(sbListOfValues, i, 1)) <> trim(sbSeparator)) then
  39.        END LOOP;--for i in 1..length(sbValues) + 1 loop
  40.   END IF;--if (nuLength = length(REPLACE(sbListOfValues, sbSeparator))) then
  41.  
  42.   RETURN sbLocated;
  43. END fsbFindToken;
  44. /

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.