sql - Split a string based on characters -

sql - Split a string based on characters -

i have next string

provider [davis hospital , medical center,1231456] not on file payer [himcd]; [aaa*y**51*c; ];

i split string 3 parts

provider = provider [davis hospital , medical center,1231456] payer = [himcd] error = [aaa*y**51*c; ]

can suggest simple sql query

declare @second_char nvarchar(10)

set @first_char = '['; set @second_char = ']';

select @first_char,@second_char declare @message varchar(max) declare @firstindex int declare @secondindex int declare @thirdindex int declare @fourthindex int set @message = 'provider[davis hospital , medical center,12345678] not on file payer [himcd]; [aaa*y**51*c; ];' set @firstindex = charindex(@first_char, @message,0) set @secondindex = charindex(@second_char,@message,0) set @thirdindex = charindex(@first_char, @message,@secondindex) set @fourthindex = charindex(@second_char,@message,@thirdindex) select @firstindex,@secondindex,@thirdindex,@fourthindex

this had. string hardcoded here table. improve ways appreciated

one alternative temporary table , cross apply

declare @t table ( string_data varchar(max) ) insert @t values ('provider [davis hospital , medical center,1231456] not on file payer [himcd]; [aaa*y**51*c; ]') select substring(string_data,idx1.pos,idx1.pos2-idx1.pos+1) first_part, substring(string_data,idx2.pos,idx2.pos2-idx2.pos+1) second_part, substring(string_data,idx3.pos,idx3.pos2-idx3.pos+1) third_part @t cross apply (select charindex('[', string_data) pos, charindex(']', string_data) pos2) idx1 cross apply (select charindex('[', string_data, idx1.pos2+1) pos, charindex(']', string_data, idx1.pos2+1) pos2) idx2 cross apply (select charindex('[', string_data, idx2.pos2+1) pos, charindex(']', string_data, idx2.pos2+1) pos2) idx3

edit saw said info table, in case, shouldn't need temp table.

sql sql-server-2012