Hi, Everyone. With SQL Server, when you want to split strings and insert them as two columns. So how to do it?

Okay for this article, you can split strings based on a hyphen symbol in a word. For example word: 343-Hello CO., LTD. and you need first column value: 343 and – is a condition for splitting and second column value: Hello CO., LTD.

SQL Server Contents:

So for this example, I have two methods show you, you can follow the below SQL query

Split String In SQL Server

Method 1 :

use SUBSTRING for splitting that based on a hyphen symbol (-)

SELECT SUBSTRING('343-Hello CO.,LTD.', 1, CASE CHARINDEX('-', '343-Hello CO.,LTD.') WHEN 0 THEN LEN('343-Hello CO.,LTD.') ELSE CHARINDEX('-', '343-Hello CO.,LTD.') - 1 END) AS FIRST_VALUE
    ,SUBSTRING('343-Hello CO.,LTD.', CASE CHARINDEX('-', '343-Hello CO.,LTD.') WHEN 0 THEN LEN('343-Hello CO.,LTD.') + 1 ELSE CHARINDEX('-', '343-Hello CO.,LTD.') + 1 END, 1000) AS SECOND_VALUE

Method 2 :

use RTRIM and REPLACE for splitting and replace value that based on a hyphen symbol (-)

SELECT RTRIM(LEFT('43-Hello CO.,LTD.',CHARINDEX('-','43-Hello CO.,LTD.')-1)) FIRST_VALUE
,REPLACE('43-Hello CO.,LTD.',RTRIM(LEFT('43-Hello CO.,LTD.',CHARINDEX('-','43-Hello CO.,LTD.'))),LTRIM('')) SECOND_VALUE

Final Result :