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

It appears you want to split the data on the second space - if that is correct then this will work:

Declare @testTable Table (testData varchar(100));  
  
 Insert Into @testTable (testData)  
 Values ('sql4 13434 22 test 39480')  
      , ('sql2 DEF 39 tests')  
      , ('data 123 258 256 tests 4587')  
      , ('sql2 FED tests');  
  
 Select col1 = ltrim(substring(tt.testData, 1, p02.pos - 2))  
      , col02 = ltrim(substring(tt.testData, p02.pos, len(tt.testData) - p02.pos + 1))  
   From @testTable                                                    As tt  
  Cross Apply (Values (charindex(' ', tt.testData, 1) + 1))           As p01(pos)  
  Cross Apply (Values (charindex(' ', tt.testData, p01.pos) + 1))     As p02(pos);  

This will fail if there are no spaces in the data - if that is the case then we can do this:

Declare @testTable Table (testData varchar(100));  
  
 Insert Into @testTable (testData)  
 Values ('sql4 13434 22 test 39480')  
      , ('sql2 DEF 39 tests')  
      , ('data 123 258 256 tests 4587')  
      , ('sql2 FED tests')  
      , ('nospaces')  
      , ('one space');  
  
 Select col1 = ltrim(substring(v.testData, 1, p02.pos - 2))  
      , col02 = ltrim(substring(v.testData, p02.pos, p03.pos - p02.pos - 1))  
   From @testTable                                                      As tt  
  Cross Apply (Values (concat(tt.testData, space(3))))                  As v(testData)  
  Cross Apply (Values (charindex(' ', v.testData, 1) + 1))              As p01(pos)  
  Cross Apply (Values (charindex(' ', v.testData, p01.pos) + 1))        As p02(pos)  
  Cross Apply (Values (charindex(' ', v.testData, p02.pos) + 1))        As p03(pos);  
  

Answer 3

--SQL Server 2017 or 2019

Create table test (Col1 varchar(900))

Insert into test values


('sql4 13434 22 test 39480'),
('sql2 DEF 39 tests'),
('data 123 258 256 tests 4587'),
('sql2 FED tests')


 ;with mycte as (
select Col1,[value],Cast([key] as int) k,  
case 
when lead([value]) over(partition by Col1 order by [key])  like'%test%'
and  try_cast([value] as int)  is not null 
then 1 
when  [value]  like'%test%'
and lag(try_cast([value] as int)) over(partition by Col1 order by [key]) is  null 
then 1
else 0 end  newkey 
from  TEST
cross apply openjson('["'+(replace(Col1,' ','","')+'"]'))
)

,mycte2 as (
select Col1 ,k, [value]
, Sum(newkey)over(partition by Col1 order by k) grp  
from mycte
 )

,mycte3 as (
select Col1,grp,  (case when grp=0 then string_agg([value],' ')  WITHIN GROUP ( ORDER BY k) else null end) col1a
, (case when grp=1 then string_agg([value],' ')  WITHIN GROUP ( ORDER BY k) else null end) col1b
 from mycte2
 group by Col1,grp
 )


 select  Col1, max(col1a) col1a , max(col1b) col1b
  from mycte3
 group by Col1 
 order by 1,2



 drop table test

Answer 4

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.

Answer 5

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