question

CharanP-5854 avatar image
0 Votes"
CharanP-5854 asked CharanP-5854 commented

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

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

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your SQL Server version?

1 Vote 1 ·
YitzhakKhabinsky-0887 avatar image
2 Votes"
YitzhakKhabinsky-0887 answered CharanP-5854 commented

Hi @CharanP-5854,

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          |
 +----+-----------------------------+--------------+----------------+






· 18
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, this looks pretty neat @YitzhakKhabinsky-0887.

Just curious, any inference on its performance impact compared to other methods?

0 Votes 0 ·

The other methods are pretty shaky.
They are based on the unreliable string parsing. One space here or there breaks it.
And I am not sure if they are working at all.

We don't have a full data set of the OP. It is up to the OP to test if the both functionality and performance are acceptable.

P.S. Please check my reply to you on LinkedIn.

1 Vote 1 ·

You should also confirm whether your text contains certain special sequences, such as ‘&’, ‘<’, ‘]]>’, ‘”’.

0 Votes 0 ·

Hi @Viorel-1,

Thanks for chiming in.
You are absolutely right on target.

I am using CDATA section as a defense mechanism for this answer on my machine.
But I had to remove it before posting my answer. The reason for that is very simple.

Unfortunately, Microsoft forum is choking on CDATA sections, and gives a dreaded error message like below:

Access Denied
You don&#39;t have permission to access &#34;http://docs.microsoft.com/answers/answers/84776/post.html&#34;; on this server.
Reference #18.d5f0f748.1599105499.39230fff

I already reported about it to Microsoft support.

Overall, I am usually impressed with your answers on the forums.
And learned a lot from you.

P.S. Please connect with me on LinkedIn.

0 Votes 0 ·

No i dont have [ < i do have only "(" ")".. thanks for trying to get into detail. Appreciate it..

0 Votes 0 ·
Show more comments

Hello @YitzhakKhabinsky-0887

How to use this inside insert select statement ?

Best Regards

0 Votes 0 ·

That should be simple as below:


;WITH rs AS
(
SELECT *
, TRY_CAST('<root><r>' +
REPLACE(col, @separator, '</r><r>') +
'</r></root>' AS XML) AS xmldata
FROM @tbl
)
INSERT INTO YOURTABLE (COL1,COL2)
SELECT CAST(xmldata.query('let $n := /root/r[.=("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[.=("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;


0 Votes 0 ·

No this wont work i guess since two from ; one for from rs and from actual table for other columns

0 Votes 0 ·
Show more comments

Hi @YitzhakKhabinsky-0887 ,

I have two questions:

1) Suppose if i have different set of combinations like : test, tests, Tests, Test, TEST, TESTS, then how to handle in the above?
2) Also, if test/s i.e,(test, tests, Tests, Test, TEST, TESTS) combination is not present it should right to col1 not in col2, how to achieve that?

Best Regards

0 Votes 0 ·

Hi @CharanP-5854 ,

1) To handle case insensitivity, you need to the following modification:

 CAST(xmldata.query('let $n := /root/r[.=("test","tests")]

should become

 CAST(xmldata.query('let $n := /root/r[lower-case(.)=("test","tests")]

I already updated the answer with it.

2) Please try to duke it out yourself.
A hint: @pos variable will have its value as zero (0).





0 Votes 0 ·
CharanP-5854 avatar image CharanP-5854 YitzhakKhabinsky-0887 ·

i tried modifying but now both columns are getting populated with out test data. its appearing in both columns.

0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered CharanP-5854 edited

Hi @CharanP-5854,

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

22575-split.png

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

Best regards
Melissa


split.png (7.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Melissa,

  1. List item

Thank you for replying.

Above is satisfying but it is not satisfying few cases as below:

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

Output should be like below

22752-image.png
Could you please help me with this?

Best Regards


0 Votes 0 ·
image.png (5.4 KiB)
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @CharanP-5854,

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:
22900-o.png


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://docs.microsoft.com/en-us/answers/articles/67444/email-notifications.html



o.png (6.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered CharanP-5854 commented

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.




· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Viorel,

Thank you for replying.

Above is satisfying but it is not satisfying few cases as below:

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

Output should be like below
COL1 COL2
sql4 13434 |22 test 39480
sql2 DEF | 39 tests
data 123 258 |256 tests 4587
sql2 FED |tests

Could you please help me with this?

Best Regards

0 Votes 0 ·

It is not clear what is the expected result. Maybe use “|” to delimit the output columns in your example.

0 Votes 0 ·

Hi,
Please see above i have given pipe symbol as starting of second column or you can refer
22650-image.png


0 Votes 0 ·
image.png (5.4 KiB)
Show more comments

It is still not clear how to split your string into 2 columns.

It looks like you are trying to split on only numbers preceding the word "test" or "tests"? If it is not a number, it is column 1?

0 Votes 0 ·

yes , it is based on string like %test% and one number before that should be populated along with that.

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered
 --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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.