Dennis Lovelady wrote:
I have a column (fooCol) in a table (footab) that contains
string data looking like the following:
'961 6'
'2809 6'
'67 6'
'66 6'
'2879 6'
'2874 6'
(No, the second set is not always 6. This is a column that was
loaded incorrectly due to lack of a comma at the source end, and
I'm trying to find an efficient way to live with it (longer
story)).
I have need of *efficiently* returning the first set of digits
and the second set of digits as separate numeric values, so that
they may be used in an UPDATE or INSERT statement or embedded SQL
where the result is numeric.
To update the numeric column which is defined as an expression
[that divides the two char tokens as separate numeric values] would
require an INSTEAD OF trigger on the VIEW which encapsulates the
expressions. Alternatively the original column can be updated as
the concatenation [similarly without the comma for consistency] of
the two fields defined by those expressions.
Any ideas? I thought of using something like select left(foocol,
instr(' ', foocol)) (which is mySQL syntax) for the "left
number", but I couldn't find an instr equivalent and, even if I
did it'd still be a string value. The second set would probably
be a no-brainer after landing on a solution for the first.
Thanks for any pointers.
Consider a review [optionally perform] of the following script
for the requests and results; note: in comparison to the given, two
blanks are assumed as well as no negative or decimal values. Doing
so may provide some [additional] ideas and\or questions for the
actual scenario:
<code>
create table s (s char(12), c char(12))
/* really needs a primary key; e.g. field K */
insert into s (s) values
('961 6'), ('2809 8'), ('67 6'), ('66 6')
,('2879 8'), ('2874 8')
create view sv (s, s_1, s_2, c) as
(select s, int(substr(s,1, locate(' ',s)-1) )
, int(substr(s, locate(' ',s)+2) ) , c
from s
)
update sv /* setup for compare; effectively: c = s */
set c = s_1 concat ' ' concat s_2
select * from sv
....+....1....+....2....+....3....+....4....+....5....+...
S S_1 S_2 C
961 6 961 6 961 6
2809 8 2,809 8 2809 8
67 6 67 6 67 6
66 6 66 6 66 6
2879 8 2,879 8 2879 8
2874 8 2,874 8 2874 8
******** End of data ********
update sv /* effectively: s_2 = S_2 - 1 */
set s = s_1 concat ' ' concat (s_2 -1 )
select * from s
....+....1....+....2....+.
S C
961 5 961 6
2809 7 2809 8
67 5 67 6
66 5 66 6
2879 7 2879 8
2874 7 2874 8
******** End of data ********
select * from sv
....+....1....+....2....+....3....+....4....+....5....+...
S S_1 S_2 C
961 5 961 5 961 6
2809 7 2,809 7 2809 8
67 5 67 5 67 6
66 5 66 5 66 6
2879 7 2,879 7 2879 8
2874 7 2,874 7 2874 8
******** End of data ********
alter table s add constraint ck_s_2nums check
( locate(' ',s) between 2 and length(rtrim(s))-2
and strip( translate( rtrim(s), '* ', ' 0123456789' )
, B ) = '**' )
insert into s values('17 7', '17 8') /* three blanks */
INSERT or UPDATE not allowed by CHECK constraint.
insert into s values('17 7', '17 8') /* only one blank */
INSERT or UPDATE not allowed by CHECK constraint.
create trigger sv_upd instead of update on sv
referencing new as n old as o
for each row
update s set s = n.s_1 concat ' ' concat n.s_2
where /* K = o.K
and */ s = o.s /* the example is missing pk on K,
so this test would update duplicate o.s values! */
create view svx (s_1, s_2, c) as
(select int(substr(s,1, locate(' ',s)-1) )
, int(substr(s, locate(' ',s)+2) )
, c
from s
)
create trigger svx_ins instead of insert on svx
referencing new as n
for each row
insert into s (/* k, */ s, c) values
( /* n.k , */ n.s_1 concat ' ' concat n.s_2
, n.c )
insert into svx values(999, 99, '999 100')
delete from svx where s_1 = 9
</code>
Regards, Chuck
midrange.com
