Wow that is brilliant thanks MM!
I have never used the APPLY operator before, but it works perfectly. I would love to say that it all makes perfect sense to me but.... Could you simply summarize what is going on with this part of the function?
select top(datalength(@input)) row_number() over (order by (select null)) as N
from (values(1),(1),(1),(1),(1),(1)) a(x), (values(1),(1),(1),(1),(1),(1)) b(x)
) Tally(N)
Thanks again!
James
That part of the code is simply building an "on-the-fly" Tally/Numbers table, which will be used to address each character in the input string.
See This Article by Jeff Moden to learn a bit more about the concept of a Tally table.
Performing this on the fly tends to be quicker than using an actual table for this sort of job, surprisingly.
The TOP() operator is there to make sure we get exactly the right amount of numbers to address each character in the string once and no more - as there is no point checking beyond the length of the input.
So, all the code does is generate a list of whole numbers from 1 to X where X is the length of the input.
Those numbers are then used in the substring to pull out each individual character, compare it to make sure it is a digit between 0 and 9 and then it can be converted to a numeric type and multiplied by it's ordinal position.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Thanks, I get the bigger picture now, but what is the "values" part doing? Why the 6 "(1)" records and the significance of the a(x) and b(x)? Sorry for being a dumb-***!
James Millar-305032 (10/25/2013)
Thanks, I get the bigger picture now, but what is the "values" part doing? Why the 6 "(1)" records and the significance of the a(x) and b(x)? Sorry for being a dumb-***!
VALUES(1),(1),(1),(1),(1),(1)
Is a Table Value Constructor, which was introduced in SQL2008 and is equivalent to this:
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
...but much easier to write and shorter!
The "a(x) and b(x)" are just table/column aliases given to the inline tables constructed by the VALUES statements.
Imagine you had a table created from the 6 UNIONed SELECTS above, called "SixOnes" for arguments sake....
Then this:
SELECT ROW_NUMBER() OVER(ORDER BY SELECT(NULL)) as N
FROM SixOnes
is equivalent to this
SELECT ROW_NUMBER() OVER(ORDER BY SELECT(NULL)) as N
FROM (VALUES(1),(1),(1),(1),(1),(1)) AS a(x)
I have two sets of VALUES, "a" and "b" so that they CROSS JOIN to produce 36 rows, which is enough to cover your 30 character string.
I could have used one VALUES() statement with 30 numbers from 1 to 30, then I could forego the ROW_NUMBER() function, but I didn't want to type that much....(starting to wish I hadn't been lazy in the first place now :hehe:)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply