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:
- DECLARE @String varchar(1000)
- SELECT
- @String = 'AV»ABC1; AC»ACTag; BB»BB1Tag; SampleInner»STag1; Not Privileged (RS)»Priv1tag; Winter 2015 Review»Windter1Tag'
- SELECT
- *,
- LEFT(VALUE, CHARINDEX('»', VALUE) - 1) AS firstname,
- SUBSTRING(VALUE, CHARINDEX('»', VALUE) + 1, LEN(VALUE) - (CHARINDEX('»', VALUE) - 1)) AS lastname
- FROM (SELECT
- SUBSTRING(';' + @String + ';', Number + 1,
- CHARINDEX(';', ';' + @String + ';', Number + 1) - Number - 1) AS VALUE
- FROM master..spt_values
- WHERE Type = 'P'
- AND Number <= LEN(';' + @String + ';') - 1
- AND SUBSTRING(';' + @String + ';', Number, 1) = ';') AS A