Substring into many field with iteration

Question

My problem is: I have a varchar-type column (IDs) with string records like this: "123056; 96445; 123100; 45893; 201971" and I want to divide them into the next fields:

 ID_1   | ID_2  | ID_3   | ID_4  | ID_5
 123056 | 96445 | 123100 | 45893 | 201971

Actually, I used this code:

update MyTable  
set ID_1 = IDs

and then:

update MYTable  
set ID_2 =    
SUBSTRING(ID_1, CHARINDEX (';', ID_1)+2, LEN(ID_1))    
where ID_1 like '%;%'

update MYTable   
set FRS_ID_1 =  
SUBSTRING(FRS_ID_1, 1, CHARINDEX (';', FRS_ID_1)-1)  
where ID_1 like '%;%'

The ID_1 like '%;%' is necessary because some fields contain only one number not a list. So I ran this code five times, manually updated the column name from ID_1 to ID_2 and etc. but it's not professional and very slow.

How could I solve it with an iteration or in another way?

Thank you.

how often do you have to do this? cause if it is once in a blue moon, you could use "text to columns" functionality of Excel. Export this table to Excel; go through "text to columns"; import back into sql db — user2065377, Sep 18 '13 at 14:05

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

You should solve it by normalising your data.

ie: By having many rows with an ID in each record.

As for how to perform the split, a search engine will provide numerous options to split a string by a character, using a function or a CTE.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 18 '13 at 14:03

podiluska

50,950
7
98
104

score 0 · Answer 2 · answered Sep 18 '13 at 14:50

Split those values into a temp table or table variable as rows.

there's plenty of split functions samples out there...granted that your output will be like this:

ID
-------
123056
-------
96445
-------
ETC.

you can then do the following query:

Select 
     Min(Case rowid When 1 Then item End) id_1,
     Min(Case rowid When 2 Then item End) id_2,
     Min(Case rowid When 3 Then item End) id_3,
     Min(Case rowid When 4 Then item End) id_4,
     Min(Case rowid When 5 Then item End) id_5
INTO #TEMP_TABLE_WITH_COLUMNS
From #temp_table_with_IDs

this is, of course, assuming that you know upfront that you'll have 5 columns...

Substring into many field with iteration

2 Answers2