I work on a SQL Server 2012 query, an I face an issue: I can't split temp table field symbol data to 5 columns.
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 column Symbol Data
into 5 columns to be
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 in the temp table column SymbolData
has a symbol or character like |
or - etc..
I need to split that column SymbolData
into 5 parts every part represent column:
The solution is easy:
\s
(space) preserving order of elementsIn order to split the values you can use XML
like this. In order to extract only numbers you can perform a a chain of REPLACE
s removing all units. In order to remove the numbers and leave the text, you can use REPLACE
s again.
In my environment, I am using a lot of SQL CLR functions and the solution looks like this:
SELECT PVT.id
,PVT.symbolData
,dbo.fn_Utils_RegexReplace ([0], '[^\d+]', '') AS [valuebefore]
,dbo.fn_Utils_RegexReplace ([0], '\d+', '') AS [unitbefore]
,[1] AS [symbole]
,dbo.fn_Utils_RegexReplace ([2], '[^\d+\.]', '') AS [valueafter]
,dbo.fn_Utils_RegexReplace ([2], '[\d+\.]', '') AS [unitafter]
FROM #TEMP
CROSS APPLY dbo.fn_Utils_RegexSplitWithOrder (SymbolData, '\s') RS
PIVOT
(
MAX([value]) FOR [index] IN ([0], [1], [2])
) PVT
ORDER BY PVT.id;
You can check this answer to get such functions in your environment, too.
In your case, it will be easier and safer to use XML to split the data and replace to shape the results.