× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.