Split Delimited String into multiple Columns in SQL Server

Question

I want to split a string in a column that is separated by space into multiple column is SQL.

I used the query below, but I get NULL values

select
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 1) AS Country_Code,
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 2) AS iso_Code,
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 3) AS status,
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 5) AS date,
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 6) AS rate,
     PARSENAME(REPLACE(FX_RAW_DATA, '  ', '.'), 7) AS fx
FROM process.FX_RAW_DATA_OUT;
GO 

Answer 1

It's not very clear from your example sample data and expected results what your data looks like. I have made some assumptions and example below should be easy to change to match your data as required.

DECLARE @stringarray varchar(max), --String holding your data
        @Splitcharc char(1), --split character, space in your example
        @X xml

set @stringarray = '01AED AUD M 30122020 .3541000 11' --example data line
set     @Splitcharc = '' -- this can be changed if needed

-- We start off by formatting your string into an XML format
set @X = CONVERT(xml,' <Table> <Row> <col>' +
REPLACE(@stringarray,@Splitcharc,'</col> <col>') + '</col></Row>   </Table> ')

-- The below show how you "query" each of the "columns" returned

SELECT     
  Tbl.cl.value('col[1]','VARCHAR(20)')  country_code,
  Tbl.cl.value('col[2]','VARCHAR(20)')  ISO_code,
  Tbl.cl.value('col[3]','VARCHAR(20)')  [status],
  Tbl.cl.value('col[4]','VARCHAR(20)')  [date],
  Tbl.cl.value('col[5]','VARCHAR(20)')  rate,
  Tbl.cl.value('col[6]','VARCHAR(20)')  fx   

 FROM @X.nodes('/Table/Row') as Tbl(cl)