SQL Split function using two characters delimiter

Question

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

Standard single char delimiter string splitting function can be used with space delimiter and you simply ignore returned rows where value is 'OR' and 'AND'. — SMor
– SMor, Commented Jul 23, 2020 at 12:27
If I'm using SQL Server 2014 Management Studio means that OP uses SQL Server 2014, then STRING_SPLIT() is not available. — Zhorov
– Zhorov, Commented Jul 23, 2020 at 12:48
String splitting in sql server is discussed every day. It matters not which version of sql server OP is using - somewhere someone has posted a function to do that for versions prior to the native tsql function as well as versions that support more than just a single character delimiter. — SMor
– SMor, Commented Jul 23, 2020 at 13:17

Gordon Linoff · Accepted Answer · 2020-07-23 11:40:18Z

7

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.

answered Jul 23, 2020 at 11:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2020-07-23 11:42:27Z

0

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
)

answered Jul 23, 2020 at 11:42

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

SQL Split function using two characters delimiter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related