根据特殊字符"|"的计数在字符串中,在单独的列中显示子字符串数据 [英] based on count of special character '|' in string ,show substring data in sperate column
问题描述
根据字符串中特殊字符"|"的计数,在单独的列中显示子字符串数据.
我的字符串= 12,美国| 10,加拿大|
期望的产量= 12美国10加拿大
即.每个在单独的列中
一栏12个
美国一栏
10合1栏
一栏加拿大
基于一个,其中值和名称是分开的,并使用|一组值和名称是分开的.
谢谢
karthik
based on count of special character ''|'' in string ,show substring data in sperate column.
my string =12,USA|10,Canada|
desired output= 12 USA 10 Canada
ie.each in seperate columns
12 in one column
USA in one column
10 in one Column
canada in one column
based one , where value and name is seperated and by using | set of value and name is seperated.
thanks
karthik
推荐答案
u可以使用拆分功能,该功能将返回与.net string().split()
相同的结果
u can use split fuction which will return same result as .net string().split()
CREATE FUNCTION [dbo].[Split](@String varchar(max), @Delimiter char(1))
returns @temptable TABLE (items varchar(max))
as
begin
declare @idx int
declare @slice varchar(max)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
此函数返回表
你称它为
从databaseName.dbo.split(12,USA | 10,Canada |'',''|'')中选择*
并再次按``,''进行如下拆分.以获得您想要的结果.
this function return a table
u call it as
select * from databaseName.dbo.split(12,USA|10,Canada|'',''|'')
and again split by '','' as follows. for your desiring result.
这篇关于根据特殊字符"|"的计数在字符串中,在单独的列中显示子字符串数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!