SELECT DISTINCT
p1.Name,p2.Name,p3.Name,p4.Name,p5.Name,p6.Name,p7.Name,p8.NAME,
  COALESCE(p1.Name,p2.Name,p3.Name,p4.Name,p5.Name,p6.Name,p7.Name,p8.Name) AS From_Name,
	i.*
FROM
	dbo.SMS_inbox AS i LEFT JOIN 
	dbo.SMS_phonebook AS p1 ON i.[from] = p1.[General phones] LEFT JOIN
	dbo.SMS_phonebook AS p2 ON i.[from] = p2.[Mobile phones] LEFT JOIN
	dbo.SMS_phonebook AS p3 ON i.[from] = p3.[Home phones] LEFT JOIN
	dbo.SMS_phonebook AS p4 ON i.[from] = p4.[Office phones] LEFT JOIN 
	dbo.SMS_phonebook AS p5 ON REPLACE(i.[from], '+62', '0') LIKE '%'+p5.[General phones]+'%' AND p5.[General phones]<>'0' LEFT JOIN
	dbo.SMS_phonebook AS p6 ON REPLACE(i.[from], '+62', '0') LIKE '%'+p6.[Mobile phones]+'%' LEFT JOIN
	dbo.SMS_phonebook AS p7 ON REPLACE(i.[from], '+62', '0') LIKE '%'+p7.[Home phones]+'%' LEFT JOIN
	dbo.SMS_phonebook AS p8 ON REPLACE(i.[from], '+62', '0') LIKE '%'+p8.[Office phones]+'%'
WHERE
  i.[text] IS NOT NULL
  
ORDER BY
  i.[datetime]

SELECT
	pb.Name,
	pb.[Mobile phones],
	t1.*,
	t2.*,
	t3.*,
	t4.*
FROM
	dbo.SMS_phonebook AS pb CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[General phones],'|'), ' ','|'),'|') AS t1 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Mobile phones],'|'), ' ','|'),'|') AS t2 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Home phones],'|'), ' ','|'),'|') AS t3 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Office phones],'|'), ' ','|'),'|') AS t4
WHERE
  pb.Name = 'Bani  KKng mBMS'

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

SELECT
	pb.Name,
	pb.[Mobile phones],
	t1.*,
	t2.*,
	t3.*,
	t4.*
FROM
	dbo.SMS_phonebook AS pb CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[General phones],'|'), ' ','|'),'|') AS t1 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Mobile phones],'|'), ' ','|'),'|') AS t2 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Home phones],'|'), ' ','|'),'|') AS t3 CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[Office phones],'|'), ' ','|'),'|') AS t4
---------------------------------------------------
SELECT
  pb.id_pb,
	pb.Name,
	pb.[General phones],
	t1.*
FROM
	dbo.SMS_phonebook AS pb CROSS APPLY
	dbo.func_explode_index(REPLACE(COALESCE(pb.[General phones],'|'), ' ','|'),'|') AS t1
ORDER BY
  pb.Name
-----------------------------------------------------------------------
-----------------------------------------------------------------------
--DECLARE @id_pb INT

--DECLARE list_cursor CURSOR FOR  
--SELECT TOP 3
--  id_pb
--FROM
--	dbo.SMS_phonebook
--ORDER BY
--  id_pb

--OPEN list_cursor  
--FETCH NEXT FROM list_cursor INTO @id_pb  

--WHILE @@FETCH_STATUS = 0  
--BEGIN  

  INSERT INTO [prib].[dbo].[SMS_phonebook_detail]
           ([id_pb]
           ,[content_detail]
           ,[category])   
  SELECT
    *
  FROM
    (
      SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      1 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[General phones],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	      --AND pb.id_pb = @id_pb
	    
	    UNION ALL
	    
	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      2 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[Mobile phones],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	      --AND pb.id_pb = @id_pb
	    
	    UNION ALL
	    
	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      3 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[Home phones],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	      --AND pb.id_pb = @id_pb
	    
	    UNION ALL
	    
	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      4 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[Office phones],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	      --AND pb.id_pb = @id_pb
	    
	    UNION ALL
	    
	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      5 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[Fax phones],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	      --AND pb.id_pb = @id_pb
	    
	    UNION ALL
	    
	    SELECT
        id_pb,
	      [Address] AS content_detail,
	      6 AS category
      FROM
	      dbo.SMS_phonebook
	    WHERE
	      COALESCE([Address],'') <> ''
	    
	    UNION ALL

	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      7 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[E-mail addresses],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	    
	    UNION ALL
	    
	    SELECT
        id_pb,
	      [Notes] AS content_detail,
	      8 AS category
      FROM
	      dbo.SMS_phonebook
	    WHERE
	      COALESCE([Notes],'') <> ''

	    UNION ALL

	    SELECT
        pb.id_pb,
	      t1.item AS content_detail,
	      9 AS category
      FROM
	      dbo.SMS_phonebook AS pb CROSS APPLY
	      dbo.func_explode_index(REPLACE(COALESCE(pb.[WWW],'|'), ' ','|'),'|') AS t1
	    WHERE
	      COALESCE(item,'') <> ''
	    
	    UNION ALL
	    
	    SELECT
        id_pb,
	      [User ID] AS content_detail,
	      10 AS category
      FROM
	      dbo.SMS_phonebook
	    WHERE
	      COALESCE([User ID],'') <> ''
	    
	    UNION ALL
	    
	    SELECT
        id_pb,
	      [PTT Address] AS content_detail,
	      11 AS category
      FROM
	      dbo.SMS_phonebook
	    WHERE
	      COALESCE([PTT Address],'') <> ''
	      
    ) AS x
  ORDER BY
    x.id_pb,
    x.category
	
--  FETCH NEXT FROM list_cursor INTO @id_pb  
--END  

--CLOSE list_cursor  
--DEALLOCATE list_cursor 

--SELECT * FROM dbo.SMS_phonebook
--SELECT * FROM dbo.SMS_phonebook_detail
--SELECT * FROM dbo.SMS_phonebook_category

SELECT *
FROM
	dbo.SMS_inbox AS si LEFT JOIN
	dbo.SMS_phonebook_detail AS spd ON si.[from]=spd.content_detail LEFT JOIN
	dbo.SMS_phonebook_detail AS spd ON si.[from]=spd.content_detail LEFT JOIN
	dbo.SMS_phonebook AS sp ON spd.id_pb = sp.id_pb
ORDER BY
  si.datetime

SELECT * FROM dbo.SMS_phonebook_detail WHERE content_detail LIKE '%8156529119'
SELECT DISTINCT [from] FROM dbo.SMS_inbox WHERE [from] NOT LIKE '+%'

SELECT content_detail,
--REVERSE(content_detail),
CASE
  WHEN category IN (1,2,3,4,5) AND LEN(content_detail)>5 THEN REVERSE(LEFT(REVERSE(content_detail), LEN(content_detail)-3))
  ELSE content_detail
END
FROM dbo.SMS_phonebook_detail

--clean phonebook
SELECT
  id_pb_detail,
  id_pb,
  content_detail,
  phone_number_fix,
  CASE
    WHEN LEFT(content_detail,1)='0' THEN '+62'+RIGHT(content_detail,LEN(content_detail)-1)
    ELSE content_detail
  END AS TEST
FROM
  dbo.SMS_phonebook_detail
WHERE
  category IN (1,2,3,4,5)
  AND content_detail NOT IN (SELECT DISTINCT [from] FROM dbo.SMS_inbox WHERE [from] NOT LIKE '+%')
  AND LEN(content_detail)>7

--UPDATE
--  dbo.SMS_phonebook_detail
--SET
--  phone_number_fix = CASE
--                        WHEN LEFT(content_detail,1)='0' THEN '+62'+RIGHT(content_detail,LEN(content_detail)-1)
--                        ELSE content_detail
--                      END
--WHERE
--  category IN (1,2,3,4,5)
--  AND content_detail NOT IN (SELECT DISTINCT [from] FROM dbo.SMS_inbox WHERE [from] NOT LIKE '+%')
--  AND LEN(content_detail)>7
---------------------------------------
SELECT
  si.datetime,
  si.[from],
  sp.Name,
  si.text
FROM
	dbo.SMS_inbox AS si LEFT JOIN
	dbo.SMS_phonebook_detail AS spd ON si.[from]=spd.phone_number_fix LEFT JOIN
	dbo.SMS_phonebook AS sp ON spd.id_pb = sp.id_pb
ORDER BY
  si.datetime

SELECT * FROM dbo.SMS_phonebook
SELECT * FROM dbo.SMS_phonebook_detail 
SELECT * FROM dbo.SMS_phonebook_category
SELECT * FROM dbo.SMS_inbox