understanding query to remove non-numeric characters

Question

I find very helpful post, how to remove non-numeric characters from string: https://stackoverflow.com/a/18635337/6216435

But few questions arose, hopefully someone can explain a bit.
1) Why to use this:

select top (100) N=row_number() over (order by @@spid) from sys.all_columns)

Results gives numbers 1-100, but why @@spid and sys.all_columns? Is there better way to do it?

2) ..where N<=datalength(DirtyCol)) [1] where C between '0' and '9' - what does [1] represents here?

Thanks for your help :)

sys.all_columns is a system table which contains metadata,it is used as a dummy table to generate data(it has many rows).For your second question [1] is an alias for that subquery it might as well have been [RandomName]- the `as` is implicit, `(subquery) as SomeName` — Mihai, Jan 24 '17 at 19:23
`[` and `]` are used around names in sql server -- for some reason this guy wanted to name this table "1" -- mostly I think it just made the code harder to read. I would have substituted `tmp` for `[1]` If it isn't a number you don't need the brackets and tmp is a good description of the table. — Hogan, Jan 24 '17 at 19:25
It would make a lot more sense to post these questions as a comment on that answer. — Hogan, Jan 24 '17 at 19:27

score 0 · Answer 1 · answered Jan 24 '17 at 19:27

That answer is using @@spid as a constant value, similar to order by (select)`

It is using sys.all_columns just for a source of 100 rows to generate the row numbers.

Here are some articles where Aaron Bertrand benchmarks alternatives for generating a set or sequence:

1 Answers1