CODEWITHSUNDEEP
sqlsql-serverdatabase
M Usama Alvi
M Usama Alvi

Reputation: 207

SQL Split function using two characters delimiter

I'm trying to break a string on the basis of words or characters of not same length. I'm using SQL Server 2014 Management Studio.

String ===> 'term1 OR term2'

So I want ti break it based on 'OR' so the result would be

term1
term2

It can also be like this

term3 AND term4

So it would bebroken on the basis of AND giving us the result

term3
term4

Upvotes: 0

Views: 2715

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Well if the extent of your use cases are splitting inputs with single OR or AND, then the base string functions can handle this:

SELECT LEFT(col, CHARINDEX(' OR ', col) - 1) AS term FROM yourTable
UNION ALL
SELECT SUBSTRING(col, CHARINDEX(' OR ', col) + 4, LEN(col)) FROM yourTable;

Data:

WITH yourTable AS (
    SELECT 'term1 OR term2' AS col
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is to use string_split() -- with a twist because it does not support multi-character separators:

select *
from string_split( replace(@str, ' OR ', '|'), '|')

Note: This assumes that | is not a valid "word" character.

Here is a db<>fiddle.

Upvotes: 7

Related Questions