I work on SQL server 2012 query I face issue : I can't split temp table field symbol data to 5 columns
so How to do that please ?
CREATE TABLE #TEMP
(
id int identity(1,1),
SymbolData NVARCHAR(50)
)
INSERT INTO #TEMP(SymbolData)
VALUES
('0Hz ~ 4.5kHz'),
('0Hz | 9kHz'),
('0V - 4.5vl'),
('0Hz . 4.5kHz')
select * from #TEMP
How to divide field Symbol Data to 5 columns to be
Expected Result
valuebefore unitbefore symbole valueafter unitafter
0 Hz ~ 4.5 Hz
0 Hz | 9 kHz
0 V - 4.5 vl
0 Hz . 4.5 kHz
Every row on temp table field symbol data have symbol or character like | or - etc..
I need to split field symbol data to 5 parts every part represent column
get value before symbol on column as value before .
SELECT td.ID
,td.SymbolData
,ValueBefore = MAX(CASE WHEN ItemNumber = 1 THEN SUBSTRING(Item,1,PATINDEX('%[^0-9.]%',Item)-1) END)
,UnitBefore = MAX(CASE WHEN ItemNumber = 1 THEN SUBSTRING(Item,PATINDEX('%[^0-9.]%',Item),LEN(ITEM)) END)
,Symbole = MAX(CASE WHEN ItemNumber = 2 THEN Item END)
,ValueAfter = MAX(CASE WHEN ItemNumber = 3 THEN SUBSTRING(Item,1,PATINDEX('%[^0-9.]%',Item)-1) END)
,UnitAfter = MAX(CASE WHEN ItemNumber = 3 THEN SUBSTRING(Item,PATINDEX('%[^0-9.]%',Item),LEN(ITEM)) END)
FROM #Temp td
CROSS APPLY dbo.DelimitedSplit8k(td.SymbolData,' ')
GROUP BY td.ID, td.SymbolData
;