How to split a string into two different fields in SQL server

Question

Hello Everyone,

I have a scenario where i need to divide a string from one column to two different columns in sql server. example:

input is:

COL 1

sql 13434 test 39480

sql2 39 tests 39

data 123 tests 4587

output should be:

COL 1 COL 2

sql 13434 test 39480

sql2 39 tests 39

data 123 tests 4587

here test and tests are common string. Could any one tell how can this be done?

Thanks in advance

Answer 1

Hi @Charan P ,

Here is a solution that is based on combination of T-SQL and XQuery.
XQuery data model is based on sequences which is very handy for the scenario, i.e. sequences of tokens in this particular case.

The algorithm is as follows:

• Variable $n is a token that belongs to the ("test","tests") sequence.
• Variable $pos is a position of the preceding token.
• Variable $num is 1 or 0 based on the preceding token data type.
• The rest is a retrieval part that is splitting sequence of tokens into two columns based on the variables above.

SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col VARCHAR(MAX));  
    INSERT INTO @tbl VALUES   
    ('sql4 13434 22 test 39480'),  
    ('sql2 DEF 39 tests'),  
    ('data 123 258 256 tests 4587'),  
    ('sql2 FED tests');  
  
DECLARE @target TABLE (ID INT PRIMARY KEY, col VARCHAR(MAX), col1 VARCHAR(100), col2 VARCHAR(100));  
    -- DDL and sample data population, end  
  
    DECLARE @separator CHAR(1) = SPACE(1);  
  
    ;WITH rs AS  
    (  
       SELECT *   
       , TRY_CAST('<root><r>' +   
     REPLACE(col, @separator, '</r><r>') +   
        '</r></root>' AS XML) AS xmldata  
       FROM @tbl  
    )  
INSERT INTO @target (ID, col, col1, col2)  
    SELECT ID, col  
     , CAST(xmldata.query('let $n := /root/r[lower-case(.)=("test","tests")]  
     let $pos := count(/root/r[. << $n[1]])  
     let $num := if (number((/root/r[$pos]/text())[1]) instance of xs:double) then 1 else 0  
     return for $x in /root/r[position() le ($pos - $num)] return data($x)') AS VARCHAR(100)) AS col1  
     , CAST(xmldata.query('let $n := /root/r[lower-case(.)=("test","tests")]  
     let $pos := count(/root/r[. << $n[1]])  
     let $num := if (number((/root/r[$pos]/text())[1]) instance of xs:double) then 1 else 0  
     return for $x in /root/r[position() gt ($pos - $num)] return data($x)') AS VARCHAR(100)) AS col2  
    FROM rs;  
  
-- test  
SELECT * FROM @target;  

Output

+----+-----------------------------+--------------+----------------+  
| ID |             col             |     col1     |      col2      |  
+----+-----------------------------+--------------+----------------+  
|  1 | sql4 13434 22 test 39480    | sql4 13434   | 22 test 39480  |  
|  2 | sql2 DEF 39 tests           | sql2 DEF     | 39 tests       |  
|  3 | data 123 258 256 tests 4587 | data 123 258 | 256 tests 4587 |  
|  4 | sql2 FED tests              | sql2 FED     | tests          |  
+----+-----------------------------+--------------+----------------+  

Answer 2

Your sample data are not really readable. Do you mean something like this?

;with data as
    (select 'sql 13434 test 39480' AS Col1
     union all select 'sql2 39 tests 39'
     union all select 'data 123 tests 4587')
select Col1 AS OldCol1, 
       left(Col1, charindex('test', Col1) - 1) as Col1,
       substring(Col1, charindex('test', Col1), len(Col1)) AS Col2
from data

Answer 3

Hi @Charan P ,

The output is not clear enough for us.

You could refer below query for all possibilities which depends on the position of space.

declare @table table   
(COL1 VARCHAR(100))  
  
INSERT INTO @table VALUES   
('sql 13434 test 39480'),  
('sql2 39 tests 39'),  
('data 123 tests 4587')  
  
SELECT * FROM @table  
  
SELECT LEFT(COL1,CHARINDEX(' ',COL1)-1) COL1,  
SUBSTRING(COL1,CHARINDEX(' ',COL1),LEN(COL1)-CHARINDEX(' ',COL1)+1) COL2  
FROM @table  
  
SELECT LEFT(COL1,CHARINDEX(' ', COL1,CHARINDEX(' ',COL1) + 1)) COL1 ,  
RIGHT(COL1,LEN(COL1)-CHARINDEX(' ', COL1,CHARINDEX(' ',COL1)+1 )) COL2  
FROM @table  
  
SELECT   REVERSE(SUBSTRING(REVERSE(COL1),CHARINDEX(' ',REVERSE(COL1)),LEN(COL1)-CHARINDEX(' ',REVERSE(COL1))+1)) COL1,  
REVERSE(LEFT(REVERSE(COL1),CHARINDEX(' ',REVERSE(COL1))-1)) COL2  
FROM @table  

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 4

Hi @Charan P ,

Please refer below query which has insert select statement:

-- DDL and sample data population, start  
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col VARCHAR(MAX));  
 INSERT INTO @tbl VALUES   
 ('sql4 13434 22 test 39480'),  
 ('sql2 DEF 39 tests'),  
 ('data 123 258 256 tests 4587'),  
 ('sql2 FED tests');  
 -- DDL and sample data population, end  
  
 select COL,  
 CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(col),CHARINDEX('tset',REVERSE(COL))+5,CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-(CHARINDEX('tset',REVERSE(COL))+5))) = 1  
 THEN REVERSE(RIGHT(REVERSE(COL),LEN(COL)-(CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5))))  
 ELSE LEFT(COL,CHARINDEX('TEST',COL)-1) END COL1,  
 CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(COL),CHARINDEX('tset',REVERSE(COL))+5,CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-(CHARINDEX('tset',REVERSE(COL))+5))) = 1  
 THEN REVERSE(LEFT(REVERSE(COL),CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-1))   
 ELSE RIGHT(COL,LEN(COL)-(CHARINDEX('TEST',COL)-1)) END COL2   
 from @tbl  
  
 create table #table   
 (col1 varchar(1000),  
 col2 varchar(1000))  
  
 insert into #table   
  select   
 CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(col),CHARINDEX('tset',REVERSE(COL))+5,CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-(CHARINDEX('tset',REVERSE(COL))+5))) = 1  
 THEN REVERSE(RIGHT(REVERSE(COL),LEN(COL)-(CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5))))  
 ELSE LEFT(COL,CHARINDEX('TEST',COL)-1) END COL1,  
 CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(COL),CHARINDEX('tset',REVERSE(COL))+5,CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-(CHARINDEX('tset',REVERSE(COL))+5))) = 1  
 THEN REVERSE(LEFT(REVERSE(COL),CHARINDEX(' ', REVERSE(COL),CHARINDEX('tset',REVERSE(COL)) + 5)-1))   
 ELSE RIGHT(COL,LEN(COL)-(CHARINDEX('TEST',COL)-1)) END COL2   
 from @tbl  
  
 select * from #table  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][2] to enable e-mail notifications if you want to receive the related email notification for this thread. [2]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html

Answer 5

Check this example too:

declare @table table ( col1 varchar(max) )  
  
insert into @table values   
( 'sql 13434 test 39480' ),  
( 'sql2 39 tests 39'),  
( 'data 123 tests 4587' ),  
( 'some longer test 123 tests 4587' )  
  
select left(col1, PATINDEX('% [0-9]% test%', col1)-1) as col1,   
       right(col1, len(col1) - PATINDEX('% [0-9]% test%', col1)) as col2  
from @table  
  
/*  
Results:  
  
col1             | col2  
------------------------------------  
sql              | 13434 test 39480  
sql2             | 39 tests 39  
data             | 123 tests 4587  
some longer test | 123 tests 4587  
*/  

If it does not work in some existing circumstances, then give details.

See also: https://social.msdn.microsoft.com/Forums/en-US/b3969b1e-66ee-44c9-9413-019448c5ced8.