You REQUIRE a Numbers table!

Splitting a string of unlimited length

Adam Machanic — Sun, 25 Jan 2009 19:44:15 GMT

There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited

Counting occurrences of a substring within a string

Adam Machanic — Sun, 25 Jan 2009 19:44:50 GMT

I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums:

Dealing with very large bitmasks

Adam Machanic — Sun, 25 Jan 2009 19:45:35 GMT

Continuing in my series of things you should probably not do in SQL Server but sometimes have to , I'm

"Reflect" a TSQL routine

Adam Machanic — Sun, 25 Jan 2009 19:53:28 GMT

Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some

Tokenize UDF

Adam Machanic — Sun, 25 Jan 2009 19:54:26 GMT

Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting.

re: You REQUIRE a Numbers table!

Chen Noam — Tue, 30 Nov 2010 01:00:05 GMT

Great article, very good performance.

Thanks,

Chen

re: You REQUIRE a Numbers table!

Joel Theophanes — Tue, 11 Oct 2011 17:13:36 GMT

Another number table generator. Not as compact as Adam's, but easy to see how many numbers are generated and doesn't rely on a system table for the initial number list.

;with t as (

select 0 as Num union

select 1 union

select 2 union

select 3 union

select 4 union

select 5 union

select 6 union

select 7 union

select 8 union

select 9

)

select (10000 * t10000.Num) + (1000 * t1000.Num) + (100 * t100.Num) + (10 * t10.Num) + t1.Num as Number

from t t1

cross join t t10

cross join t t100

cross join t t1000

cross join t t10000

order by Number

re: You REQUIRE a Numbers table!

Adam Machanic — Mon, 17 Oct 2011 01:50:54 GMT

Nice, Joel. I use the following technique these days, which I learned a while back from Itzik Ben-Gan:

---

DECLARE @number_of_numbers INT = 100000;

;WITH

a AS (SELECT 1 AS i UNION ALL SELECT 1),

b AS (SELECT 1 AS i FROM a AS x, a AS y),

c AS (SELECT 1 AS i FROM b AS x, b AS y),

d AS (SELECT 1 AS i FROM c AS x, c AS y),

e AS (SELECT 1 AS i FROM d AS x, d AS y),

f AS (SELECT 1 AS i FROM e AS x, e AS y),

numbers AS

(

SELECT TOP(@number_of_numbers)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number

FROM f

)

SELECT *

FROM numbers

---

re: You REQUIRE a Numbers table!

joining to a numbers table — Thu, 17 Jan 2013 22:34:54 GMT

Hi,

I'd like to joint to a numbers table using the row_number value. WITHOUT using a CTE. I'm interested to see if it's faster and possible without CTE. I can't think of a way to join the ROW_NEMBER Value to the Number table.

Thanks for reading.

re: You REQUIRE a Numbers table!

Adam Machanic — Sat, 19 Jan 2013 21:10:22 GMT

Sure, you can do it without a CTE. Use a derived table.

--Adam