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 |