hello group,
pl/sql is to oracle;
______ is to mysql?
thanks in regards,
kray
Patrick Newberry <[EMAIL PROTECTED]> wrote:
Have mysql problem as follows:
when the if statement below is executed, it seems fine unless a space
follows an "&", "." or "of".
The whole procedure is listed below with some samples of the data below
this if statement.
Basically it drops element after the space if the element before the
space contains one of the strings listed above.
Thus Alfred E. Newman
drops the Newman
where as
You Only Live Twice
does not drop anything.
Here is the code we think is dropping the element.
IF INSTR(v_record_r,' ') <> 0
THEN
SET v_element = SUBSTR(v_record_r, 1, INSTR(v_record_r,'
')-1); -- parce out lead element
SET v_record_r = SUBSTR(v_record_r,
LENGTH(SUBSTR(v_record_r, 1, INSTR(v_record_r,' ')+1)));
ELSE
SET v_element = v_record_r;
-- load last element
SET v_record_r = NULL;
END IF;
Example
Data:
INSERT INTO tbl_rtv (rtv) VALUES ('You Only Live Twice');
INSERT INTO tbl_rtv (rtv) VALUES ('Live & Let Die');
INSERT INTO tbl_rtv (rtv) VALUES ('Alfred E. Newman');
with each word in this table:
INSERT INTO tbl_element (element) VALUES ('Newman');
INSERT INTO tbl_element (element) VALUES ('Die');
INSERT INTO tbl_element (element) VALUES ('E.');
INSERT INTO tbl_element (element) VALUES ('&');
etc....
Output from procedure:
+------------------------------------------------------------+----------
-----------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | v_record
|
+------------------------------------------------------------+----------
-----------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | You Only
Live Twice |
+------------------------------------------------------------+----------
-----------+
1 row in set (0.03 sec)
+----+-----------+-----------------+
| ID | v_element | remainder |
+----+-----------+-----------------+
| 32 | You | Only_Live_Twice |
+----+-----------+-----------------+
1 row in set (0.03 sec)
+----+-----------+------------+
| ID | v_element | remainder |
+----+-----------+------------+
| 21 | Only | Live_Twice |
+----+-----------+------------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 15 | Live | Twice |
+----+-----------+-----------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 29 | Twice | NULL |
+----+-----------+-----------+
1 row in set (0.03 sec)
This one does not Work:
+------------------------------------------------------------+----------
------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | v_record
|
+------------------------------------------------------------+----------
------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | Live &
Let Die |
+------------------------------------------------------------+----------
------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 15 | Live | &_Let_Die |
+----+-----------+-----------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 1 | & | + |
+----+-----------+-----------+
1 row in set (0.03 sec)
+------+-----------+-----------+
| ID | v_element | remainder |
+------+-----------+-----------+
| NULL | + | Die |
+------+-----------+-----------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 8 | Die | NULL |
+----+-----------+-----------+
1 row in set (0.03 sec)
another example that does not work:
+------------------------------------------------------------+----------
--------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | v_record
|
+------------------------------------------------------------+----------
--------+
| ABSOLUTELY BRAND SPANKING NEW RECORD TO VERIFY ELEMENTS OF | Alfred E.
Newman |
+------------------------------------------------------------+----------
--------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 2 | Alfred | E._Newman |
+----+-----------+-----------+
1 row in set (0.03 sec)
+----+-----------+-----------+
| ID | v_element | remainder |
+----+-----------+-----------+
| 9 | E. | |
+----+-----------+-----------+
1 row in set (0.03 sec)
+------+-----------+-----------+
| ID | v_element | remainder |
+------+-----------+-----------+
| NULL | | NULL |
+------+-----------+-----------+
1 row in set (0.03 sec)
Complete procedure:
DROP PROCEDURE IF EXISTS verify_record;
DELIMITER //
CREATE PROCEDURE verify_record
( IN v_record VARCHAR(255)
, INOUT v_errors VARCHAR(50)
)
BEGIN
--
-- VARIABLES
DECLARE v_record_r VARCHAR(255);
DECLARE v_element VARCHAR(255);
DECLARE v_elementid INT;
--
DECLARE element_existance INT DEFAULT 1;
--
-- CURSORS
DECLARE elementid_cur
CURSOR FOR
SELECT elementid
FROM tbl_element
WHERE element = v_element;
--
-- HANDLERS
DECLARE CONTINUE HANDLER FOR NOT FOUND SET element_existance = 0;
--
-- BEGIN ACTION
SET v_record_r = v_record;
--
-- loop through record
WHILE v_record_r IS NOT NULL
DO
IF INSTR(v_record_r,' ') <> 0
THEN
SET v_element = SUBSTR(v_record_r, 1, INSTR(v_record_r,'
')-1); -- parce out lead element
SET v_record_r = SUBSTR(v_record_r,
LENGTH(SUBSTR(v_record_r, 1, INSTR(v_record_r,' ')+1)));
ELSE
SET v_element = v_record_r;
-- load last element
SET v_record_r = NULL;
END IF;
--
-- determine valid elements
OPEN elementid_cur;
FETCH elementid_cur INTO v_elementid;
CLOSE elementid_cur;
SELECT v_elementid ID, v_element, REPLACE(v_record_r, ' ','_')
remainder;
--
-- keep track of the missing
IF element_existance = 0
THEN
SET element_existance = 1;
INSERT INTO tbl_missing_element
( element
)
VALUES(v_element
);
END IF;
--
-- clear variable
SET v_elementid = NULL;
END WHILE;
END;
//
DELIMITER ;
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
SPONSORED LINKS
American general life and accident insurance company American general
life insurance company American general life American general mortgage
American general life insurance Computer internet security
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "php_mysql" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
__ _
____ | |(_)
/ || | __ _ ,_ _______
| -- | || |> \/'\/ || __ |
\ \ | || || +-+-+ || ___/
/+__\ \| || || | | | | \ \
< > copyryt 2003 (r)
---------------------------------
What are the most popular cars? Find out at Yahoo! Autos
[Non-text portions of this message have been removed]
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
