Here is a better solution created by my friend Shaun Batterton. SQL> with one as ( select 'This is it' x from dual),
2 two as ( select 'ThiS is not' x from dual) 3 select sum(decode(substr(one.x, level, 1) , substr(two.x, level, 1), 0, 1)) cnt_diff 4 from one,two 5 connect by level<= greatest(length(one.x), length(two.x)); On Thu, Sep 29, 2011 at 3:47 PM, Michael Moore <[email protected]>wrote: > You could do this if you really hated the world ... > > SELECT COUNT(*) > FROM (SELECT * > FROM (SELECT ROWNUM a_rnum, SUBSTR(nums, 1, INSTR(nums, ',') - 1) > a_num > FROM (SELECT n, SUBSTR(val, 1 + INSTR(val, ',', 1, n)) nums > FROM (SELECT ROWNUM AS n, list.val > FROM (SELECT > REGEXP_REPLACE('[email protected]', > '(.)', ',\1') > || ',' > val > FROM DUAL) list > CONNECT BY LEVEL < > LENGTH(list.val) - > LENGTH(REPLACE(list.val, > ',', ''))))) a > FULL OUTER JOIN > (SELECT ROWNUM b_rnum, SUBSTR(nums, 1, INSTR(nums, ',') - 1) > b_num > FROM (SELECT n, SUBSTR(val, 1 + INSTR(val, ',', 1, n)) nums > FROM (SELECT ROWNUM AS n, list.val > FROM (SELECT REGEXP_REPLACE(' > [email protected]', > '(.)', ',\1') > || ',' > val > FROM DUAL) list > CONNECT BY LEVEL < > LENGTH(list.val) - > LENGTH(REPLACE(list.val, > ',', ''))))) b > ON a_rnum = b_rnum) > WHERE a_num != b_num OR a_num IS NULL OR b_num IS NULL; > > > On Thu, Sep 29, 2011 at 2:40 PM, ddf <[email protected]> wrote: > >> >> >> On Sep 28, 9:34 pm, Naveen <[email protected]> wrote: >> > string compare >> > >> > >> > >> > >> > >> > On Wed, Sep 28, 2011 at 8:18 AM, Ninja Li <[email protected]> wrote: >> > > Hi, >> > >> > > I have the following two email addresses: >> > >> > > [email protected] >> > > [email protected] >> > >> > > The third and fourth characters of the two email addresses are not >> > > matching. >> > >> > > Is there any way to use SQL to get the number of the characters not >> > > matching ( 2 ) in the the two string, e.g. using regexp functions? or >> > > I have to use PL/SQL. >> > >> > > Thanks in advance. >> > >> > > Nick >> > >> > > -- >> > > You received this message because you are subscribed to the Google >> > > Groups "Oracle PL/SQL" group. >> > > To post to this group, send email to [email protected] >> > > To unsubscribe from this group, send email to >> > > [email protected] >> > > For more options, visit this group at >> > >http://groups.google.com/group/Oracle-PLSQL?hl=en >> > >> > -- >> > naveen- Hide quoted text - >> > >> > - Show quoted text - >> >> That is a rather brief response, with no examples provided. Please >> provide an example so we can all see what this response means. >> >> >> David Fitzjarrell >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
