Skip to content
Advertisement

SQL – split numeric into 2 columns?

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?

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement