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

Please provide sample data and desired results as tabular text. — GMB
– GMB, Commented Dec 31, 2020 at 18:27
Parsename() allows for only 4 positions. Take a peek at stackoverflow.com/questions/64232640/… — John Cappelletti
– John Cappelletti, Commented Dec 31, 2020 at 18:36
Show data examples please, but if a value you run almost any function on in SQL the results will be NULL. — Brad
– Brad, Commented Dec 31, 2020 at 18:36
Sample Data FX_RAW_DATA 01AED AUD M 30122020 .3541000 11 01AED BRL M 30122020 1.4138300 11 — ovie
– ovie, Commented Dec 31, 2020 at 18:47
Expected result Country_Code iso_Code status date rate fx 01AED AUD M 30122020 0.3541 11 — ovie
– ovie, Commented Dec 31, 2020 at 18:48

Charlieface · Accepted Answer · 2022-09-06 13:26:08Z

You don't state what version of SQL Server you are running. If STRING_SPLIT is not available on your version, you can do as follows (I think you are trying to split column FX_RAW_DATA on table process). Basically, you apply the calculation of each space one after the other, then substring each:

SELECT
    Country_Code = SUBSTRING(p.FX_RAW_DATA, 1, v1.chr - 1),
    iso_Code =     SUBSTRING(p.FX_RAW_DATA, v1.chr + 1, v2.chr - v1.chr - 1),
    status =       SUBSTRING(p.FX_RAW_DATA, v2.chr + 1, v3.chr - v2.chr - 1),
    date =         SUBSTRING(p.FX_RAW_DATA, v3.chr + 1, v4.chr - v3.chr - 1),
    rate =         SUBSTRING(p.FX_RAW_DATA, v4.chr + 1, v5.chr - v4.chr - 1),
    fx =           SUBSTRING(p.FX_RAW_DATA, v5.chr + 1, LEN(p.FX_RAW_DATA))

FROM process p

CROSS APPLY (VALUES(CHARINDEX(' ', p.FX_RAW_DATA)) v1(chr)
CROSS APPLY (VALUES(CHARINDEX(' ', p.FX_RAW_DATA, v1.chr + 1)) v2(chr)
CROSS APPLY (VALUES(CHARINDEX(' ', p.FX_RAW_DATA, v2.chr + 1)) v3(chr)
CROSS APPLY (VALUES(CHARINDEX(' ', p.FX_RAW_DATA, v3.chr + 1)) v4(chr)
CROSS APPLY (VALUES(CHARINDEX(' ', p.FX_RAW_DATA, v4.chr + 1)) v5(chr);

The first substring always starts at 1 and ends at the first break, the last substring you can just pass the full length to get the remainder of the string.

user7415753 · Accepted Answer · 2021-01-01 00:19:07Z

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)

Collectives™ on Stack Overflow

Split Delimited String into multiple Columns in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related