July 24, 2013 at 2:14 pm
Hi All - I came across Jeff Moden's Splitter Code earlier today and have been trying to run the function against a space delimited field in my database. When running the function against the function does not seem to recognize the space as a delimiting character.
Sample Data
CoreID ComputerID ResultsText
1 1456 192.168.1.1 192.168.1.2 192.168.1.3
2 1458 192.168.1.8 192.168.1.6 192.168.1.5
Here is what I am running against this table:
Select sd.CoreID, sd.ComputerID, sd.ResultsText
From SampleData sd
cross apply dbo.DelimitedSplit8k(sd.ResultsText, ' ')
Additionally I have tried the following and it did not work either:
Select sd.CoreID, sd.ComputerID, sd.ResultsText
From SampleData sd
cross apply dbo.DelimitedSplit8k(sd.ResultsText, SPACE(1))
The output that results from both of these is below (as you can see it is not splitting out the field at all):
CoreID ComputerID ResultsText ItemNumber Item
1 1456 192.168.1.1 192.168.1.2 192.168.1.3 1 192.168.1.1 192.168.1.2 192.168.1.3
2 1458 192.168.1.8 192.168.1.6 192.168.1.5 1 192.168.1.8 192.168.1.6 192.168.1.5
Any help would be greatly appreciated. Not sure what is not working here....
July 24, 2013 at 2:21 pm
You might have a character that might not be a space.
You can check which character is with this code (it's untested, but it should work).
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
SELECT SUBSTRING(ResultsText, N, 1), ASCII(SUBSTRING(ResultsText, N, 1))
FROM SampleData
CROSS JOIN cteTally
ORDER BY ResultsText, N
July 24, 2013 at 2:39 pm
Luis Cazares (7/24/2013)
You might have a character that might not be a space.You can check which character is with this code (it's untested, but it should work).
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
SELECT SUBSTRING(ResultsText, N, 1), ASCII(SUBSTRING(ResultsText, N, 1))
FROM SampleData
CROSS JOIN cteTally
ORDER BY ResultsText, N
States that it is a line feed - ASCII 10.
I tried this:
Select sd.CoreID, sd.ComputerID, sd.ResultsText
From SampleData sd
cross apply dbo.DelimitedSplit8k(sd.ResultsText, ascii(10))
Still is a no go. Get's the same result as I previously did.
July 24, 2013 at 2:40 pm
Resolved it myself whoops! This works:
Select sd.CoreID, sd.ComputerID, sd.ResultsText
From SampleData sd
cross apply dbo.DelimitedSplit8k(sd.ResultsText, char(10))
Thanks Luis!
July 24, 2013 at 2:48 pm
You're welcome. I'm glad I could help 🙂
July 24, 2013 at 8:48 pm
james.rosser (7/24/2013)
Resolved it myself whoops! This works:
Select sd.CoreID, sd.ComputerID, sd.ResultsText
From SampleData sd
cross apply dbo.DelimitedSplit8k(sd.ResultsText, char(10))
Thanks Luis!
I don't see where you're actually using anything from the function. Are you sue it's doing what you want?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2013 at 6:36 am
It is working like a champ and doing what it needs to do. Thanks for checking in.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply