October 21, 2010 at 5:18 pm
Hi,
I need some help. I already have something like this:
DECLARE @Custom_Properties varchar(100)
SET @Custom_Properties = 'AA : 11'
SELECT LEFT(@Custom_Properties, CHARINDEX(':', @Custom_Properties) - 1) AS [Property],
RIGHT(@Custom_Properties, CHARINDEX(':', REVERSE(@Custom_Properties))-1) AS [Value]
Output:
Property Value
AA 11
But I want to make the query for a string like, 'AA : 11, AB : 12, AC : 13'. I do not have any idea of how to implement the CASE statement or a loop...
October 21, 2010 at 6:00 pm
Here's the DelimitedString8k function that's been heavily worked on round these parts. Modify to suit your needs. I didn't create it, didn't work on it, and have copy/pasta'd for your convenience. All thanks to Jeff Moden for this version. I'd just point you at the proper thread/post but I can't seem to find it.
CREATE FUNCTION dbo.DelimitedSplit8K
/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a
bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and
compactness. Extra speed realized (cuts out 9 SELECT/UNION ALL's) with UNPIVOT thanks to
Gianluca Sartori.
6. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Base 10 redaction for CTE.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
Rev 02 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the
SELECT List.
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS ( --=== Create Ten 1's very quickly
SELECT N
FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0
UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt
), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 6:51 pm
Craig Farrell (10/21/2010)
Here's the DelimitedString8k function that's been heavily worked on round these parts. Modify to suit your needs. I didn't create it, didn't work on it, and have copy/pasta'd for your convenience. All thanks to Jeff Moden for this version. I'd just point you at the proper thread/post but I can't seem to find it.
Here is the latest version of the Delimited Split Function
To use it with your sample:
declare @test varchar(50);
set @test = 'AA : 11, AB : 12, AC : 13';
SELECT ds1.ItemNumber,
Property = MAX(CASE WHEN ds2.ItemNumber % 2 = 1 THEN ds2.Item ELSE NULL END),
Value = MAX(CASE WHEN ds2.ItemNumber % 2 = 0 THEN ds2.Item ELSE NULL END)
FROM dbo.DelimitedSplit8K(@test, ',') ds1
CROSS APPLY dbo.DelimitedSplit8K(ltrim(rtrim(ds1.Item)), ':') ds2
GROUP BY ds1.ItemNumber
ORDER BY ds1.ItemNumber;
Results:
ItemNumber Property Value
-------------------- -------- --------
1 AA 11
2 AB 12
3 AC 13
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 21, 2010 at 11:35 pm
WayneS (10/21/2010)
Craig Farrell (10/21/2010)
Here's the DelimitedString8k function that's been heavily worked on round these parts. Modify to suit your needs. I didn't create it, didn't work on it, and have copy/pasta'd for your convenience. All thanks to Jeff Moden for this version. I'd just point you at the proper thread/post but I can't seem to find it.Here is the latest version of the Delimited Split Function
To use it with your sample:
declare @test varchar(50);set @test = 'AA : 11, AB : 12, AC : 13';
SELECT ds1.ItemNumber,
Property = MAX(CASE WHEN ds2.ItemNumber % 2 = 1 THEN ds2.Item ELSE NULL END),
Value = MAX(CASE WHEN ds2.ItemNumber % 2 = 0 THEN ds2.Item ELSE NULL END)
FROM dbo.DelimitedSplit8K(@test, ',') ds1
CROSS APPLY dbo.DelimitedSplit8K(ltrim(rtrim(ds1.Item)), ':') ds2
GROUP BY ds1.ItemNumber
ORDER BY ds1.ItemNumber;
Results:
ItemNumber Property Value-------------------- -------- --------
1 AA 11
2 AB 12
3 AC 13
Nicely done, Wayne! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 8:21 am
I made simple function that make what I wanted. The problem is that, I dont know how to do with a table like this,
ID Value
1 AA : 11, BB : 22, CC : 33
2 DD : 44, AB : 12, AC : 13
3 EE: 55, BA : 21, BB : 13
I only can use the function one row at time... otherwise give me an error like:
'Subquery returned more than 1 value.'
Thanks for that code that helped!
October 22, 2010 at 1:46 pm
ja0 (10/22/2010)
I made simple function that make what I wanted. The problem is that, I dont know how to do with a table like this,ID Value
1 AA : 11, BB : 22, CC : 33
2 DD : 44, AB : 12, AC : 13
3 EE: 55, BA : 21, BB : 13
I only can use the function one row at time... otherwise give me an error like:
'Subquery returned more than 1 value.'
Thanks for that code that helped!
Let's see your function so we can show you how with a Cross Apply.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 3:17 pm
Here is it:
ALTER FUNCTION [dbo].[Split]
(
@RowData nvarchar(max),
@Split1 nvarchar(1),
@Split2 nvarchar(1)
)
RETURNS @RtnValue TABLE
(
[Property] nvarchar(2000),
[Value] nvarchar(500)
)
AS
BEGIN
DECLARE @Data nvarchar(max)
DECLARE @Property nvarchar(2000)
DECLARE @DataValue nvarchar(500)
WHILE (Charindex(@Split1,@RowData)>0)
BEGIN
-- Put in a single Row the a delimited item
Set @Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@Split1,@RowData)-1)))
-- Take the left side of the data (Ex. ANSI Pressure Rating : 600 -> ANSI Pressure Rating)
Set @Property = ltrim(rtrim(LEFT(@Data, CHARINDEX(@Split2, @Data)-1) ))
-- Take the right side of the data
Set @DataValue = ltrim(rtrim(RIGHT(@Data, CHARINDEX(@Split2, REVERSE(@Data))-1) ))
INSERT INTO @RtnValue ([Property], [Value])
SELECT
[Property] = @Property, [Value] = @DataValue
SET @RowData = Substring(@RowData,Charindex(@Split1,@RowData)+1,len(@RowData))
END
-- Last Value is not followed by comma
INSERT INTO @RtnValue ([Property], [Value])
SELECT [Property] = ltrim(rtrim(LEFT(@RowData, CHARINDEX(@Split2, @RowData)-1))),
[Value] = ltrim(rtrim(RIGHT(@RowData, CHARINDEX(@Split2, REVERSE(@RowData))-1) ))
RETURN
END
October 22, 2010 at 4:27 pm
Ok... we'll handle this in a couple of steps. First, we need some test data. Take a look at the first link in my signature line at the bottom of the thread for how to correctly post test data. I use those same methods in the following to use the data you provided as "non consumable" test data and convert it into something we can test with. Do take the time to read article at the link i pointed out... it's got some other handy stuff in it, as well.
--=============================================================================
-- Create some test data. Note that NONE of the code in this section is
-- a part of the solution. It''s just to build test data.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('TempDB..#JBMTest','U') IS NOT NULL
DROP TABLE #JBMTest
;
--===== Create the test table
CREATE TABLE #JBMTest
(
ID INT,
Value VARCHAR(1000) --Whatever ;-)
)
;
--===== Populate the test table with data
INSERT INTO #JBMTest
(ID,Value)
SELECT 1,'AA : 11, BB : 22, CC : 33' UNION ALL
SELECT 2,'DD : 44, AB : 12, AC : 13' UNION ALL
SELECT 3,'EE: 55, BA : 21, BB : 13'
;
Now that we have some persistent test data, we can use your dbo.Split function to solve the problem of splitting all the data in the table using CROSS APPLY...
--=============================================================================
-- Split the data using the original Split function provided by ja0.
--=============================================================================
SELECT source.ID, split.Property, Value = CAST(split.Value AS INT)
FROM #JBMTest source
CROSS APPLY dbo.Split(Value,',',':') split
That little slice of computational heaven give us the following result set...
ID Property Value
----------- ---------- -----------
1 AA 11
1 BB 22
1 CC 33
2 DD 44
2 AB 12
2 AC 13
3 EE 55
3 BA 21
3 BB 13
(9 row(s) affected)
If you want and don't mind slow code, you could leave it at that. Across a lot of rows, it's going to be slow (because of the WHILE LOOP doing multiple inserts) PLUS it doesn't return the "postion" of each element in the string (which may not be necessary in this case because you only care about the "Property" name).
I'll be back soon with some alternative code that will run a wee bit faster but, in the mean time, you might want to take a gander at the following article.
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 4:46 pm
WayneS (10/21/2010)
Here is the latest version of the Delimited Split Function
DelimitedSplit8k... d'oh. My bad. Thank you Wayne. It's now (finally) stored in my 'favorites' folder.
Jeff and yourself always continue to impress me with some of your techniques. I'm starting to feel like the Codeburglar.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 22, 2010 at 8:16 pm
Craig Farrell (10/21/2010)
Here's the DelimitedString8k function that's been heavily worked on round these parts. Modify to suit your needs. I didn't create it, didn't work on it, and have copy/pasta'd for your convenience. All thanks to Jeff Moden for this version. I'd just point you at the proper thread/post but I can't seem to find it.
CREATE FUNCTION dbo.DelimitedSplit8K/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a
bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and
compactness. Extra speed realized (cuts out 9 SELECT/UNION ALL's) with UNPIVOT thanks to
Gianluca Sartori.
6. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Base 10 redaction for CTE.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
Rev 02 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the
SELECT List.
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS ( --=== Create Ten 1's very quickly
SELECT N
FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0
UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt
), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
I found out later on that the UNPIVOT in that function sometimes behaves quite badly on certain machines and I've abandoned its use. In usually use the function that Wayne has provided a link to for those places that won't actually let me use a Tally Table or where the DBA doesn't understand that not all reads are bad.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 8:22 pm
By the way, if you do decide to use the DelimitedSplit8K function (which, of course, has lots of uses), here's how to solve the same problem using it... and it pretty much smokes the WHILE LOOP version...
SELECT source.ID,
LTRIM(RTRIM(SUBSTRING(split.Item,1,CHARINDEX(':',split.Item)-1))) AS Property,
LTRIM(RTRIM(SUBSTRING(split.Item,CHARINDEX(':',split.Item)+1,8000))) AS Value
FROM #JBMTest source
CROSS APPLY dbo.DelimitedSplit8K(Value,',') split
There are some additional enhancements that I've been studying for the DelimitedSplit8k function. I'll post the new function somewhere once I've tested it well enough. In the meantime, the link that WayneS provided in one of his previous posts above contains the latest "released" version. A lot of good folks had some great input to it over time.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 8:33 pm
Craig Farrell (10/22/2010)
WayneS (10/21/2010)
Here is the latest version of the Delimited Split FunctionDelimitedSplit8k... d'oh. My bad. Thank you Wayne. It's now (finally) stored in my 'favorites' folder.
Jeff and yourself always continue to impress me with some of your techniques. I'm starting to feel like the Codeburglar.
Heh... "Codeburglar"... Like I said in the Tally Table article, I didn't invent it. I'm just spreading the word. Burglar-away.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 9:03 pm
If you want and don't mind slow code, you could leave it at that. Across a lot of rows, it's going to be slow (because of the WHILE LOOP doing multiple inserts) PLUS it doesn't return the "postion" of each element in the string (which may not be necessary in this case because you only care about the "Property" name).
I'll be back soon with some alternative code that will run a wee bit faster but, in the mean time, you might want to take a gander at the following article.
Interesting approach using Cross Apply.
I am just trying to learn and apply the learned one step at time. So, after I make what I want in any form I just look for improvement.
In other words, I will be looking further on the link provided.
Thanks!
October 22, 2010 at 10:37 pm
With that good thought in mind, here's a link to all of Paul White's articles. For the current problem at hand, take a look at his two articles on CROSS APPLY. They teach what CROSS APPLY is and how it works better than most articles I've seen on the subject.
http://www.sqlservercentral.com/Authors/Articles/Paul_White/46040/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2010 at 5:17 am
Craig Farrell (10/22/2010)
WayneS (10/21/2010)
Here is the latest version of the Delimited Split FunctionDelimitedSplit8k... d'oh. My bad. Thank you Wayne. It's now (finally) stored in my 'favorites' folder.
You're welcome. Always glad to help those trying to help themselves.
Jeff and yourself always continue to impress me with some of your techniques. I'm starting to feel like the Codeburglar.
LOL! Yeah, you ought to see all the neat code that I've snagged from here myself - mostly from Jeff. I use a combination of "Clippings" (a FireFox add-in), and SSMS templates to keep them all organized - I have very few bookmarks saved.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply