根据字符拆分字符串

I have the following string

我有以下字符串

Provider [DAVIS HOSPITAL AND MEDICAL CENTER,1231456] was not on file for Payer [HIMCD];  [AAA*Y**51*C; ];

I would like to split this string into 3 parts

我想将这个字符串分成3部分

Provider = Provider [DAVIS HOSPITAL AND MEDICAL CENTER,1231456] 
Payer = [HIMCD]
Error = [AAA*Y**51*C; ]

Can anyone suggest a simple sql query

任何人都可以建议一个简单的SQL查询

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 AND MEDICAL CENTER,12345678] was not on file for 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 is what I had. The string is hardcoded here but usually its from a table. Any better ways would be appreciated

这就是我所拥有的。字符串在这里是硬编码的,但通常来自表格。任何更好的方法将不胜感激

1 个解决方案

#1

One option is a temporary table and CROSS APPLY

一个选项是临时表和CROSS APPLY

DECLARE @T table
(
    string_data VARCHAR(MAX)
)
INSERT INTO @T VALUES ('Provider [DAVIS HOSPITAL AND MEDICAL CENTER,1231456] was not on file for Payer [HIMCD];  [AAA*Y**51*C; ]')

SELECT SUBSTRING(string_data,Idx1.pos,Idx1.pos2-Idx1.pos+1) as first_part,
       SUBSTRING(string_data,Idx2.pos,Idx2.pos2-Idx2.pos+1) AS second_part,
       SUBSTRING(string_data,Idx3.pos,Idx3.pos2-Idx3.pos+1) AS third_part
FROM @T
CROSS APPLY (SELECT CHARINDEX('[', string_data) AS pos, CHARINDEX(']', string_data) AS pos2) AS Idx1
CROSS APPLY (SELECT CHARINDEX('[', string_data, Idx1.pos2+1) AS pos, CHARINDEX(']', string_data, Idx1.pos2+1) AS pos2) AS Idx2
CROSS APPLY (SELECT CHARINDEX('[', string_data, Idx2.pos2+1) AS pos, CHARINDEX(']', string_data, Idx2.pos2+1) AS pos2) AS Idx3

EDIT Just saw that you said the data is from a table, in which case, you shouldn't need the temp table.

编辑只是看到你说数据来自一个表,在这种情况下,你不应该需要临时表。

#1