split function in pl/sql

Question

User-1075092149 posted

if my field save in database as the format of yyyymmdd 0:00

how can i separte the date and time and save to variable in pl/sql? (oracle)

----------------------

a field contain yyyymmdd 0:00 (date with time)

var a=yyyymmdd

var b=0:00

how can i separte a field into 2 variable.

Answer 1

User1630798415 posted

you can use this function

CREATE FUNCTION dbo.fnSplit(

    @sInputList VARCHAR(8000) -- List of delimited items

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

) RETURNS @List TABLE (item VARCHAR(8000))



BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

 BEGIN

 SELECT

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 

 IF LEN(@sItem) > 0

  INSERT INTO @List SELECT @sItem

 END



IF LEN(@sInputList) > 0

 INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

GO

--Test

select * from fnSplit('1,22,333,444,,5555,666', ',')

select * from fnSplit('1##22#333##444','##')  --note second item has embedded #

select * from fnSplit('1 22 333 444  5555 666', ' ')

Answer 2

User-578610739 posted

Hi Member,

you can split value by date format also.

SELECT CASE
       WHEN START_DATE BETWEEN TO_DATE (
                                TO_CHAR (START_DATE, 'MM/DD/YYYY')
                                || ' 07:00:00',
                                'MM/DD/YYYY HH24:MI:SS'
                            )
                        AND  TO_DATE (
                                 TO_CHAR (START_DATE, 'MM/DD/YYYY')
                                 || ' 11:59:00',
                                 'MM/DD/YYYY HH24:MI:SS'
                             )
       THEN
           'TIME1'
   END

see link,

http://stackoverflow.com/questions/11069328/split-date-field-into-time-and-day-oracle

https://forums.oracle.com/forums/thread.jspa?threadID=2049162