SQL - split numeric into 2 columns?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

In this example, I used left for the Split1, and show the values past the 4th position for the Split2:
I've included a testing temporary table to hold our the testing values.
Feel free to adjust the code to work with your situation.

DECLARE @thelist TABLE
(
LeadKey      int
);

INSERT INTO @thelist (LeadKey)
select 1 union all
select 2 union all
select 35566 union all
select 5522 union all
select 83746623

select cast(x1.LeadKey as varchar(19)),
Left(x1.LeadKey, 4) as 'Split1',
(case when len(x1.LeadKey) > 4 then right(x1.LeadKey, len(x1.LeadKey) - 4)
 else '' end
 ) as 'Split2'

from @thelist as x1