USE [STAGING]
GO
/****** Object:  UserDefinedFunction [dbo].[func_split_alamat]    Script Date: 03/16/2015 16:41:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Author:		  Pribadi Pamungkas
-- Create date: 2014-08-08
-- Last edit:   2015-03-16 by Pribadi Pamungkas
-- Description:	Function untuk memecah alamat menjadi 4 baris, lalu diambil berdasarkan baris keberapa (1 sd 4)
-- Format:      func_split_alamat(<alamat VARCHAR>, <index INT>)
-- Contoh:      SELECT dbo.func_split_alamat('Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fringilla vel, aliquet nec, vulputate eget, arcu.', 2)


ALTER FUNCTION [dbo].[func_split_alamat]
(
	@pi_alamat VARCHAR(4000),
	@pi_indeks TINYINT
)
RETURNS VARCHAR(100)
AS
BEGIN

  DECLARE 
    @p_jumlah_karakter INT,
    @po_hasil VARCHAR(100)
  SET @p_jumlah_karakter = 100  -- Jumlah maksimal karakter di tiap kolom (kecuali kolom 4)
  
  DECLARE @po_ret_table TABLE (
      baris_1     VARCHAR(100),
      baris_2     VARCHAR(100),
      baris_3     VARCHAR(100),
      baris_4     VARCHAR(100)
  );
  
  WITH data (col) AS (
    SELECT @pi_alamat
  )
  
  INSERT INTO @po_ret_table
  SELECT
    RTRIM(col1) AS col1, RTRIM(col2) AS col2, col3, col4
  FROM data
    CROSS APPLY (
      SELECT
        CASE
          WHEN LEN(col)>@p_jumlah_karakter THEN
            NULLIF((@p_jumlah_karakter + 1) - CHARINDEX(' ', REVERSE(LEFT(col, (@p_jumlah_karakter + 1)))), 0)
          ELSE LEN(col)
        END
    ) AS x1 (first_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col, ISNULL(first_space_pos, (@p_jumlah_karakter))),
        LTRIM(NULLIF(SUBSTRING(col, ISNULL(first_space_pos + 1, (@p_jumlah_karakter)), 4000), ''))
    ) AS x2 (col1, col23)
    CROSS APPLY (
      SELECT
        NULLIF((@p_jumlah_karakter + 1) - CHARINDEX(' ', REVERSE(LEFT(col23, (@p_jumlah_karakter + 1)))), 0)
    ) AS x3 (second_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col23, ISNULL(second_space_pos, (@p_jumlah_karakter))),
        LTRIM(NULLIF(SUBSTRING(col23, ISNULL(second_space_pos + 1, (@p_jumlah_karakter)), 4000), ''))
    ) AS x4 (col2, col34)
    CROSS APPLY (
      SELECT
        NULLIF((@p_jumlah_karakter + 1) - CHARINDEX(' ', REVERSE(LEFT(col34, (@p_jumlah_karakter + 1)))), 0)
    ) AS x5 (third_space_pos)
    CROSS APPLY (
      SELECT
        LEFT(col34, ISNULL(third_space_pos, (@p_jumlah_karakter))),
        LTRIM(NULLIF(SUBSTRING(col34, ISNULL(third_space_pos + 1, (@p_jumlah_karakter)), 4000), ''))
    ) AS x6 (col3, col4)

  SELECT 
    @po_hasil=CASE @pi_indeks
                WHEN 1 THEN baris_1
                WHEN 2 THEN baris_2
                WHEN 3 THEN baris_3
                WHEN 4 THEN baris_4
              END
  FROM @po_ret_table
  
	-- Return the result of the function
	RETURN @po_hasil

END
