Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

SQL select

arizio
arizio Member Posts: 58 Blue Ribbon
edited Oct 26, 2009 6:39AM in SQL & PL/SQL
Hi all,
I need an help for a SQL query.

I have 2 UNIXTIME dates

D1:1146767400 (Thu, 4 May 2006 18:30:00 UTC)

D2:1146768300 (Thu, 4 May 2006 18:45:00 UTC)



I need now to create the string D3 which has the char Ni only if Ni is both in D1 and D2 --> resulting D3: "114676?????"


Thanks,
Riccardo

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,930 Red Diamond
    Answer ✓
    Hi, Riccardo,

    If d1 and d2 are NUMBERs, start by using TO_CHAR to convert them to strings.
    Assuming d1 and d2 are strings:
    WITH  test_data 	AS
    (
    	select	'1146767400'	AS d1
    	,	'1146768300'	AS d2
    	FROM	dual
    )
    SELECT	RPAD ( MAX (SUBSTR (d1, 1, LEVEL))
    	     , LENGTH (d1)
    	     , '?'
    	     )
    FROM	test_data
    WHERE	SUBSTR (d1, 1, LEVEL)
          = SUBSTR (d2, 1, LEVEL)
    CONNECT BY	LEVEL <= LENGTH (d1)
    ;
    I feel there must be a better way.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,930 Red Diamond
    Answer ✓
    Hi, Riccardo,

    If d1 and d2 are NUMBERs, start by using TO_CHAR to convert them to strings.
    Assuming d1 and d2 are strings:
    WITH  test_data 	AS
    (
    	select	'1146767400'	AS d1
    	,	'1146768300'	AS d2
    	FROM	dual
    )
    SELECT	RPAD ( MAX (SUBSTR (d1, 1, LEVEL))
    	     , LENGTH (d1)
    	     , '?'
    	     )
    FROM	test_data
    WHERE	SUBSTR (d1, 1, LEVEL)
          = SUBSTR (d2, 1, LEVEL)
    CONNECT BY	LEVEL <= LENGTH (d1)
    ;
    I feel there must be a better way.
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    with tab as (
    select '1146767400' col1 ,'1146768300' col2 from dual
    )
    select column_value from tab t,xmltable('
    declare function local:charbreak 
      ( $arg as xs:string? )  as xs:string* {       
       for $ch in string-to-codepoints($arg)
       return codepoints-to-string($ch)
     } ;
    declare function local:myfunc($seq as xs:string* , $seq1 as xs:string*) as xs:string
    {
      if(empty($seq) or ($seq[position()=1] != $seq1[position()=1]) or empty($seq1)) 
      then ""  
      else fn:concat( $seq[position()=1], local:myfunc($seq[position()!=1],$seq1[position()!=1])    )
    };
    local:myfunc(
      local:charbreak($str1),local:charbreak($str2)
    )
    '  passing xmlelement(col1,t.col1) as "str1",xmlelement(col2,t.col2) as "str2")
    An another way....can't say it is efficient

    Ravi Kumar
This discussion has been closed.