SQL - split numeric into 2 columns?

Jess8766 0
Viewed 44

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?

sql-server-2016 sql
2 Answers

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

DannySlor0

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
Ivar Harris0
Related