Nikunj Satasiya

Nikunj Satasiya

  • 559
  • 2.8k
  • 798.7k

Split records based on multiple separator

Mar 18 2019 2:04 AM
Hello, Everyone, I Need Help to Prepare Query in PostgreSQL
 
I required your help to prepare query in PostgreSQL.

Below is my input.

Here you can find that in Field column has a unique value and Values column has values with multiple separators.

Now I want below output.
 
Here are the steps:

1. First value should be separated with ";" and generate new column with SplitFields
2. From Spitalfields we have to again separate it with ">>" and in that first value should be considered as Folder and Second Value should be considered as Value

Please help to prepare the query.

I have prepared the same query in SQL server but in PostgreSQL, it didn't work for me as I have no more Idea about PostgreSQL

Here is QUERY for SQL Server:
  1. DECLARE @String varchar(1000)  
  2. SELECT  
  3. @String = 'AV»ABC1; AC»ACTag; BB»BB1Tag; SampleInner»STag1; Not Privileged (RS)»Priv1tag; Winter 2015 Review»Windter1Tag'  
  4. SELECT  
  5. *,  
  6. LEFT(VALUE, CHARINDEX('»', VALUE) - 1) AS firstname,  
  7. SUBSTRING(VALUE, CHARINDEX('»', VALUE) + 1, LEN(VALUE) - (CHARINDEX('»', VALUE) - 1)) AS lastname  
  8. FROM (SELECT  
  9. SUBSTRING(';' + @String + ';', Number + 1,  
  10. CHARINDEX(';'';' + @String + ';', Number + 1) - Number - 1) AS VALUE  
  11. FROM master..spt_values  
  12. WHERE Type = 'P'  
  13. AND Number <= LEN(';' + @String + ';') - 1  
  14. AND SUBSTRING(';' + @String + ';', Number, 1) = ';'AS A  

Answers (1)