CODEWITHSUNDEEP
sqlsql-serversql-server-2012
JohnB
JohnB

Reputation: 4359

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

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?

Upvotes: 9

Views: 63783

Answers (4)

NoNaMe
NoNaMe

Reputation: 6222

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.

Upvotes: 6

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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

Upvotes: 5

Related Questions