本文介绍了在SQL中分割字段值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想拆分一个字段值(用分号分隔)并将其保存在数组中
例如:
I wanna split a field value(seperated by semicolon) and save it in an array
Eg :
189;217;189;199;
并帮助我一个个地检索它并保存在临时表中
喜欢
row_num--value
--- 1 ------- 189
--- 2 ------- 217
--- 3 ------- 189
--- 4 ------- 199
And help me to retrieve it one by one and save on a temporary table
Like
row_num--value
---1-------189
---2-------217
---3-------189
---4-------199
推荐答案
DECLARE @param VARCHAR(MAX) = '189;217;189;199;';
SET @param = SUBSTRING(@param,0,LEN(@param))
DECLARE @Xparam XML;
SELECT @Xparam = CAST('<i>' + REPLACE(@param,';','</i><i>') + '</i>' AS XML)
SELECT ROW_NUMBER() OVER( ORDER BY x.i.value('.','INT')) ROWNUM,x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)
谢谢
Thank you
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
现在,如下调用此函数,
Now, call this function like below,
select * from dbo.parsevalues('189;217;189;199;',';') where val<>''
祝您编码愉快!
:)
Happy Coding!
:)
这篇关于在SQL中分割字段值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!