SQL - split numeric into 2 columns?

Question

I am trying to split some numeric keys in my table into separate columns (to help save space in SSAS, lower cardinality)

My data looks like the below..

LeadKey
1
2
3
5522
83746623

I want to split these into 2 columns... with 4 digits in each column. (where applicable, as anything 1>9999 won't have anything populated in the 2nd column)

So an example output of the above would be the below..

LeadKey         Split1            Split2
1                  1
2                  2
35566              3556             6
5522               5522    
83746623           8374            6623

How could I achieve this? I have split columns easily before using substring and a known character.. but never had to do a split like this. Does anyone have an approach to handle this?

Answer 1

Here is a solution in case you have the LeadKey numbers as int.

select LeadKey
      ,left(LeadKey, 4) Split1
      ,right(LeadKey, case when len(LeadKey)-4 < 0 then 0 else len(LeadKey)-4 end) Split2
from   t

LeadKey	Split1	Split2
1	1
2	2
35566	3556	6
5522	5522
83746623	8374	6623

Fiddle