How to split symbol data field on temp table to 5 columns?

Transact-SQL
,
1

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 .

get unit before symbol on column as unit before .

get symbol on column as symbol .

get value after symbol on column as value after .

get unit after symbol on column as unit after .

2

Wasnt a similar question you posed already answered for you? Did you try that?

3

hi

i tried to do this ... got this far !! hope this helps !!

i will try for the others .. also ..

please click arrow to the left for Create Data 
----------------------
-- create table 

CREATE TABLE TEMP
(
id int identity(1,1),
SymbolData  NVARCHAR(50)
)
go 

---------------------------
-- insert table 

INSERT INTO TEMP(SymbolData)
VALUES 
('0Hz ~ 4.5kHz'),
('0Hz | 9kHz'),
('0V - 4.5vl'),
('0Hz . 4.5kHz')

go

select *
    ,left(SymbolData,PATINDEX('%[^0-9]%',symboldata )-1) 
	, right(SymbolData,PATINDEX('%[^0-9]%',symboldata )+1) 
	from temp

image

4

This does the whole shebang...

 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
;

Get the nasty fast DelimitedSplit8K function from the "RESOURCES" section of the article at the following link...
https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

1 Like