在SQL中分割字段值 [英] split a field value in 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

推荐答案

尝试一下...

Try this ...

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

在您的数据库中创建此功能

Create this function in your database

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!
:)

先生. goo0gle搜索给了我

使用字符串分割字符串的简单而有效的方法Transact-SQL [

Mr. goo0gle search gave me

An Easy But Effective Way to Split a String using Transact-SQL[^]

这篇关于在SQL中分割字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！