использование REGEXP_INSTR

SilverM
Дата: 20.11.2008 12:59:06
помогите, плиз)))
с помощью REGEXP_INSTR сравнить два слова
и определить длину одинаковой части в начале:
'Иванов' и 'Иванопуло' - общая часть 'Ивано' 5 символов
Elic
Дата: 20.11.2008 13:12:27
Это задача не для regexp-а :)
SilverM
Дата: 20.11.2008 13:26:02
Жаль(((
_Alex_SMIRNOV_
Дата: 20.11.2008 16:37:57
Немного кривовато, но....
WITH s AS (SELECT 'ivanovich' word1, 'ivanov' word2 FROM dual)
SELECT word1, word2, ident_size
  FROM (SELECT CASE WHEN SUBSTR(word1, 1, LEVEL) <> SUBSTR(word2, 1, LEVEL) THEN 0 ELSE LENGTH(SUBSTR(word2, 1, LEVEL)) END ident_size,
               word1,
               word2,
               connect_by_isleaf is_l
          FROM s 
        CONNECT BY SUBSTR(word1, 1, LEVEL) =  SUBSTR(word2, 1, LEVEL)
       )
  WHERE is_l = 1
SY
Дата: 20.11.2008 19:15:34
_Alex_SMIRNOV_
Немного кривовато, но....

Well, a lot кривовато :)

with t as (
           select  'Ivanov'    a,
                   'Ivanopulo' b
             from  dual
          )
select  substr(a,1,level) common_left
  from  t
  where connect_by_isleaf = 1
  start with substr(a,1,1) = substr(b,1,1)
  connect by substr(a,level,1) = substr(b,level,1)
/

COMMON
------
Ivano

SY.
Elic
Дата: 20.11.2008 19:33:32
SY
select  substr(a,1,level) common_left
  from  t
  where connect_by_isleaf = 1
  start with substr(a,1,1) = substr(b,1,1)
  connect by substr(a,level,1) = substr(b,level,1)
Короче и с большим охватом версий:
select max(substr(a, 1, level-1)) common_left --, max(substr(a, 1, level-1)) keep (dense_rank last order by level)
  from  t
  connect by substr(a, level-1, 1) = substr(b, level-1, 1)
SY
Дата: 20.11.2008 19:50:08
Elic
Короче и с большим охватом версий:


с большим охватом версий - yes, but at the cost of a sort. Короче - yes, but at the cost of extra iteration (which is not that important for the task, but still..):

with t as (
           select  'Ivanov'    a,
                   'Ivanopulo' b
             from  dual
          )
select  level,substr(a,1,level) common_left
  from  t
--  where connect_by_isleaf = 1
  start with substr(a,1,1) = substr(b,1,1)
  connect by substr(a,level,1) = substr(b,level,1)
/

LEVEL COMMON
----- ------
    1 I
    2 Iv
    3 Iva
    4 Ivan
    5 Ivano

ed
 file afiedt.buf

with t as (
           select  'Ivanov'    a,
                   'Ivanopulo' b
             from  dual
          )
select level,substr(a, 1, level-1) common_left
  from  t
  connect by substr(a, level-1, 1) = substr(b, level-1, 1)
/

LEVEL COMMON
----- ------
    1
    2 I
    3 Iv
    4 Iva
    5 Ivan
    6 Ivano

SY.
SilverM
Дата: 21.11.2008 16:41:32
Ясно, спасибо)))
Guest_2211
Дата: 22.11.2010 08:42:02
Доброго утра!
Подскажите, как сравнить две строки и определить длину одинаковой части в начале
в Oracle9i ?
-2-
Дата: 22.11.2010 09:04:20
Guest_2211,

Для однобайта
select utl_raw.compare(utl_raw.cast_to_raw(s1), utl_raw.cast_to_raw(s2))-1, 
       length(ltrim(utl_raw.cast_to_varchar2(utl_raw.bit_xor(utl_raw.cast_to_raw(s1), utl_raw.cast_to_raw(s2))),chr(0)))  
from t;