locked
split function in pl/sql RRS feed

  • 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.

    Tuesday, August 28, 2012 3:02 AM

Answers

  • 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', ' ')
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2012 5:02 AM

All replies

  • 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', ' ')
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2012 5:02 AM
  • 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

    Tuesday, August 28, 2012 9:30 AM