How do I split a string into 2 parts based on a delimiter?

Question

I have table in a SQL Server database with the following columns:

Field1, Field2, Field3

Field1 is a column of type string with '|' as a separator.

It has the following form:

Part1|Part2

I'd like to write a SQL query which returns the following columns:

Part1, Part2, Field2, Field3

What is the best way to do that?

You should ***never ever*** store multiple values into a single column - this violates even the **first normal form** of database design and will cause you endless grief and trouble - just don't do it — marc_s, Feb 27 '17 at 06:52
is it really this simple ? select replace('Part1|Part2','|',',') Field1, 'Field2' Field2, 'Field3' Field3 — KumarHarsh, Feb 27 '17 at 10:09

score 3 · Answer 1 · answered Feb 27 '17 at 06:44

One option here is to make use of SQL Server's base string functions SUBSTRING() and CHARINDEX() to split apart the two parts of Field1 on the pipe character.

SELECT SUBSTRING(Field1, 1, CHARINDEX('|', Field1)-1) AS Part1,
       SUBSTRING(Field1, CHARINDEX('|', Field1)+1, LEN(Field1)) AS Part2,
       Field2,
       Field3
FROM yourTable

score 1 · Answer 2 · answered Feb 27 '17 at 09:29

The advantage of this approach(besides being short), is that it will not fail, if the pipe is missing.

SELECT
  SUBSTRING(Field1, 0, charindex('|',Field1)) Part1,
  STUFF(Field1,1,charindex('|',Field1),'') Part2,
  Field2,
  Field3
FROM (values('abc|def','val2','val3')) x(Field1, field2, Field3)

score 0 · Answer 3 · edited May 23 '17 at 12:09

Let me start by saing I totally agree with Marc_s's comment - Never ever store multiple values in a single column.
For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutly yes!

Now, assuming you can't change the database structure, here is one way to do it using Left and Right with charIndex

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
( 
    Col varchar(10)
)

INSERT INTO @T VALUES 
('a|b'),
('ab|cd'),
('abc|def'),
('abcd|efgh'),
('abcde|fghi')

The Query:

SELECT  Col,
        LEFT(Col, CHARINDEX('|', Col)-1) As Part1,
        RIGHT(Col, LEN(Col) - CHARINDEX('|', Col)) As Part2
FROM @T

Results:

Col             Part1   Part2
a|b             a       b
ab|cd           ab      cd
abc|def         abc     def
abcd|efgh       abcd    efgh
abcde|fghi      abcde   fghi

score 0 · Accepted Answer · answered Jan 03 '19 at 14:00

0

Here is a simple function that could be used to split string in DB:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

it will return all the values by splitting with space.

answered Jan 03 '19 at 14:00

NoNaMe

6,020
30
82
110

How do I split a string into 2 parts based on a delimiter?

4 Answers4

Linked