MSSQL 截取数据

ALTER Function [dbo].[fn_StrToTable](
@str varchar(max),
@Separator1 VARCHAR(10) -----第一级别的间隔符号

)
Returns @tableName Table
(
columnName varchar(max)
)
As
Begin
if @Separator1 is null or @Separator1=''
begin
set @Separator1=','
end
set @str = @str+@Separator1
Declare @insertStr varchar(max)
Declare @newstr varchar(max)
set @insertStr = left(@str,charindex(@Separator1,@str)-1)
set @newstr = stuff(@str,1,charindex(@Separator1,@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
set @insertStr = left(@newstr,charindex(@Separator1,@newstr)-1)
Insert @tableName Values(@insertStr)
set @newstr = stuff(@newstr,1,charindex(@Separator1,@newstr),'')
end
Return
End

 

用法：

 

  Declare @str nvarchar(max)
set @str='{"imageUrl_Extend":"@/upload/images/2018/11/8e74929fbae98809.jpg,@/upload/images/2018/11/39899ba3a34897c2.jpg,@/upload/images/2018/11/94bad2a6bec588f6.jpg","titleFormatString":"False_False_False_","checkUserName":"admin","checkDate":"2018-11-15 10:38","checkReasons":""}'
 select REPLACE(SUBSTRING(columnName,0,CHARINDEX(',',columnName)),'"','') from(
 select * from [dbo].[fn_StrToTable](@str)
 )tb where columnName like '%.jpg%' or  columnName like '%.png%'

Declare @str nvarchar(max)
set @str='{"imageUrl_Extend":"@/upload/images/2018/11/8e74929fbae98809.jpg,
@/upload/images/2018/11/39899ba3a34897c2.jpg,@/upload/images/2018/11/94bad2a6bec588f6.jpg",
"titleFormatString":"False_False_False_","checkUserName":"admin","checkDate":"2018-11-15 10:38","checkReasons":""}'

select * from [dbo].[fn_StrToTable](@str,'')

select REPLACE(SUBSTRING(columnName,0,CHARINDEX(',',columnName)),'"','') from(
select * from [dbo].[fn_StrToTable](@str,'')
)tb where columnName like '%.jpg%' or columnName like '%.png%'

 

  select T1= REPLACE( SUBSTRING(columnName,0,  CHARINDEX(  ';',columnName)),'"','') 
  from(
select columnName from [dbo].[fn_StrToTable]('229323;Joy|2294;Joang','|')
)tb

 

 

结果：