January 8, 2019 at 9:37 am
Hello. I have a COLUMN that I'm trying to break apart into separate fields. This column is delimited by a ':' Below is the Column String.
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
Select SubString(@string, (CHARINDEX(':', @string, 0) + 1),
(CharIndex(':', RIGHT(@string, (LEN(@string) - (CharIndex(':', @string, 0)))), 0) - 1)) As CUM_CA
I also tried this and was able to get the first Column but now sure how to use charindex to get the rest of the columns needed:
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
select SUBSTRING(@string, (CHARINDEX(':',@string,0) + 1), 5) AS CUM_CA
What I'm looking for is 4 new Columns:
Cum_CA -- 12.0
Cum CE -- 12.0
Cum QP -- 42.00
Cum QPA -- 3.500
These should be in separate columns/fields.
January 8, 2019 at 9:50 am
Hi,
It seems you should make use of in-built function String_split .
Thanks.
January 8, 2019 at 9:58 am
Thanks for responding however STRING_SPLIT is not a valid object name.
January 8, 2019 at 10:29 am
ettentrala - Tuesday, January 8, 2019 9:58 AMThanks for responding however STRING_SPLIT is not a valid object name.
SELECT * FROM STRING_SPLIT('a,b,c', ',')
Were you using it properly, or, are you sure you are running SQL 2016 or better???
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 8, 2019 at 10:47 am
I'm using it properly. Using SQL Server Management studio 2017
January 8, 2019 at 10:50 am
ettentrala - Tuesday, January 8, 2019 10:47 AMI'm using it properly. Using SQL Server Management studio 2017
SQL Server Management Studio 2017 has nothing to do with the version of SQL Server you are running.
Can you run this, and post the results?
SELECT @@VERSION
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 8, 2019 at 11:03 am
Assuming that you are not running at least SQL 2016 SP1, this will help you
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 8, 2019 at 12:16 pm
My apologizes, I was wrong,
Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Oct 3 2017 14:56:10 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
January 8, 2019 at 1:14 pm
ettentrala - Tuesday, January 8, 2019 12:16 PMMy apologizes, I was wrong,Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Oct 3 2017 14:56:10 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Well, Jeff Moden's string splitter is probably your best bet!
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
SELECT *
FROM [dbo].[DelimitedSplit8K] (@string, '.')
Results:
| ItemNumber | ItemValue |
| 1 | Cum CA:12 |
| 2 | 0 CE:12 |
| 3 | 0 QP:42 |
| 4 | 00 QPA:3 |
| 5 | 500 |
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 8, 2019 at 1:43 pm
I able to do this.
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
select
CumCA
,
CE,
QP,
QPA
from
(
select
r.value('(./RecordType)[1]','varchar(100)') RecordType,
r.value('(./RecordValue)[1]', 'varchar(100)') RecordValue
from
(
(select
cast('<Records><Record><RecordType>' + replace(replace(replace(@string, 'Cum CA', 'CumCA'), ':', '</RecordType><RecordValue>'), ' ', '</RecordValue></Record><Record><RecordType>') + '</RecordValue></Record></Records>' as XML) as tmpXML
) as tmp
cross apply tmpXML.nodes('/Records/Record') t(r)
)
) as PivotData
pivot
(
max(RecordValue)
for RecordType in (CumCA, CE, QP, QPA)
) as PivotResult
January 8, 2019 at 2:06 pm
Michael L John - Tuesday, January 8, 2019 1:14 PMettentrala - Tuesday, January 8, 2019 12:16 PMMy apologizes, I was wrong,Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Oct 3 2017 14:56:10 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Well, Jeff Moden's string splitter is probably your best bet!
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
SELECT *
FROM [dbo].[DelimitedSplit8K] (@string, '.')Results:
ItemNumber ItemValue 1 Cum CA:12 2 0 CE:12 3 0 QP:42 4 00 QPA:3 5 500
There has been an update to the splitter that uses LEAD() and is about 50% faster IIRC. That is your best alternative. I believe that there is a link to the update in Jeff's article.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 8, 2019 at 2:49 pm
drew.allen - Tuesday, January 8, 2019 2:06 PMThere has been an update to the splitter that uses LEAD() and is about 50% faster IIRC. That is your best alternative. I believe that there is a link to the update in Jeff's article.Drew
Drew,
Went to Jeff's article and didn't seem it has the link to your updated version. Maybe something to follow up with Jeff - unless I am blind 😀
January 8, 2019 at 3:08 pm
drew.allen - Tuesday, January 8, 2019 2:06 PMMichael L John - Tuesday, January 8, 2019 1:14 PMettentrala - Tuesday, January 8, 2019 12:16 PMMy apologizes, I was wrong,Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Oct 3 2017 14:56:10 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Well, Jeff Moden's string splitter is probably your best bet!
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
SELECT *
FROM [dbo].[DelimitedSplit8K] (@string, '.')Results:
ItemNumber ItemValue 1 Cum CA:12 2 0 CE:12 3 0 QP:42 4 00 QPA:3 5 500 There has been an update to the splitter that uses LEAD() and is about 50% faster IIRC. That is your best alternative. I believe that there is a link to the update in Jeff's article.
Drew
Since the OP wants these in separate columns - and the string has multiple delimiters - it gets a bit trickier. If we can assume that the values are always in the specified order:
Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500';
Select @string
, max(Case When i.ItemNumber = 2 Then i.Item End) As Cum_CA
, max(Case When i.ItemNumber = 3 Then i.Item End) As Cum_CE
, max(Case When i.ItemNumber = 4 Then i.Item End) As Cum_QP
, max(Case When i.ItemNumber = 5 Then i.Item End) As Cum_QPA
From (
Select dsk.ItemNumber
, dsk2.Item
From dbo.DelimitedSplit8K(@string, ':') dsk
Cross Apply dbo.DelimitedSplit8K(dsk.Item, ' ') dsk2
Where dsk.ItemNumber > 1
And dsk2.ItemNumber = 1
) As i
If the values in the string can be in any order it gets much more difficult using a traditional string splitter. Using the XML splitter method would be easier:
Declare @string varchar(max) = 'Cum CE:12.0 CA:12.0 QPA:42.00 QP:3.500'
Select CA
, CE
, QP
, QPA
From (
Select r.value('(./RecordType)[1]', 'varchar(100)') RecordType
, r.value('(./RecordValue)[1]', 'varchar(100)') RecordValue
From ((Select cast('<Records><Record><RecordType>'
+ replace(replace(replace(@string, 'Cum ', ''), ':', '</RecordType><RecordValue>'), ' ', '</RecordValue></Record><Record><RecordType>')
+ '</RecordValue></Record></Records>' As xml) As tmpXML
) As tmp
Cross Apply tmpXML.nodes('/Records/Record') t(r)
)) As PivotData
Pivot (max(RecordValue) For RecordType In (CA, CE, QP, QPA)) As PivotResult;
If the column values are not known - then it becomes much more difficult...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2019 at 3:21 pm
If you don't know the order - and want just the first 4 values:
Select @string
, max(Case When i.ItemNumber = 1 And i.Item2 = 2 Then i.Item End) As Field1
, max(Case When i.ItemNumber = 2 And i.Item2 = 1 Then i.Item End) As Field1Value
, max(Case When i.ItemNumber = 2 And i.Item2 = 2 Then i.Item End) As Field2
, max(Case When i.ItemNumber = 3 And i.Item2 = 1 Then i.Item End) As Field2Value
, max(Case When i.ItemNumber = 3 And i.Item2 = 2 Then i.Item End) As Field3
, max(Case When i.ItemNumber = 4 And i.Item2 = 1 Then i.Item End) As Field3Value
, max(Case When i.ItemNumber = 4 And i.Item2 = 2 Then i.Item End) As Field4
, max(Case When i.ItemNumber = 5 And i.Item2 = 1 Then i.Item End) As Field4Value
From (
Select dsk.ItemNumber, dsk2.ItemNumber As Item2
, dsk2.Item
From dbo.DelimitedSplit8K(@string, ':') dsk
Cross Apply dbo.DelimitedSplit8K(dsk.Item, ' ') dsk2
) As i
This does not give you the actual column names - but does provide the fields and field values...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 9, 2019 at 7:13 am
ettentrala - Tuesday, January 8, 2019 1:43 PMI able to do this.Declare @string Varchar(max) = 'Cum CA:12.0 CE:12.0 QP:42.00 QPA:3.500'
select
CumCA
,
CE,
QP,
QPA
from
(
select
r.value('(./RecordType)[1]','varchar(100)') RecordType,
r.value('(./RecordValue)[1]', 'varchar(100)') RecordValue
from
(
(select
cast('<Records><Record><RecordType>' + replace(replace(replace(@string, 'Cum CA', 'CumCA'), ':', '</RecordType><RecordValue>'), ' ', '</RecordValue></Record><Record><RecordType>') + '</RecordValue></Record></Records>' as XML) as tmpXML
) as tmp
cross apply tmpXML.nodes('/Records/Record') t(r)
)
) as PivotData
pivot
(
max(RecordValue)
for RecordType in (CumCA, CE, QP, QPA)
) as PivotResult
Converting strings to XML requires the use of "enlargement concatenation" and that makes such code comparatively horribly slow. My recommendation is to avoid "XML Splitters" unless you receive the data as XML.
There are a lot of tests out there that seem to prove otherwise. That's because most people that do such tests use the same data for every row rather than variable data. The "Devil in the Data" is the fact that they've created a table with super low cardinality, which the XML splitter method does quite well at. Change it to more real data and something that should take about 22 seconds to run will suddenly take about 8 minutes to run when you deploy it to production.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply