How to split nvarchar(max) string inside SQL

Question

I've got nvarchar(max) column with values alike '((A1 OR A2) AND (A4 AND A5)) OR A6)' , I need to select 'A1','A2','A4','A5','A6' From it.

How can I spilt on SQL syntax ? is that possible ?

Answer 1

Try something like this.

Sample table

declare @t table (id int identity, str nvarchar(max))
insert @t select '((A1 OR A2) AND (A4 AND A5)) OR A6)'
insert @t select '(A1 OR A2)'

This breaks the varchar into the words, and numbers them

select id, word,
    row_number() over (partition by id order by number) position
from
(
    select t.id, v.number,
        substring(t.str+')',
                  v.number+1,
                  patindex('%[() ]%',
                           substring(t.str+')',
                                     v.number+1,
                                     1000))-1) word
    from @t t
    inner join master..spt_values v on v.type='P'
      and v.number < len(t.str)
      and (v.number=0 or substring(t.str,v.number,1) like '[() ]')
) x
where word not in ('','OR','AND')

Output

id   word  position
1    A1    1
1    A2    2
1    A4    3
1    A5    4
1    A6    5
2    A1    1
2    A2    2

Answer 2

you could try this solution t-sql opposite to string contatenation...

or this one

SQL User Defined Function to Parse a Delimited String