Forum Stats

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

Discussions

Categories

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
· Share on TwitterShare on Facebook

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.
    · Share on TwitterShare on Facebook

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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.