如何在sqlserver 2008中拆分多个字符串值 [英] How to split the Multiple strings values in sqlserver 2008

问题描述

这里我需要在下面的过程中拆分2个字符串。如何在sql server中拆分这两个字符串。

可以任何人知道，给我回复....

Hi,
here i need spliting 2 strings in below process. How can i split these two strings in sql server.
could any one know, send me the reply for this....

STRING 'A,B,C,D'
STRING '1,2,3,4'

APLHA  NUMB
A       1
B       2
C       3
D       4

问候

Nanda Kishore.CH

Regards
Nanda Kishore.CH

推荐答案

你必须自己处理它，那里没有内置的机制。但它并不复杂：使用逗号分隔值参数SQL IN子句中的字符串 [ ^ ]显示了一个用于处理单个CSV字符串的函数，您可以轻松地将其调整为两个。

You will have to handle it yourself, there is no built in mechanism for that. But it''s not that complex: Using comma separated value parameter strings in SQL IN clauses[^] shows a function designed to handle a single CSV string, you could easily adapt it to do two.

创建将使用的此函数for parse values

Create this function that will used for parse values

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(max))
AS
BEGIN
    DECLARE @Value varchar(max)
    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

查询使用...

Query to use...

select a.val as Alpha ,b.val as Nums from dbo.parsevalues('a,b,c,d',',')  as a
left join dbo.parsevalues('1,2,3,4',',')  as b on a.id=b.id

快乐的编码！

：）

Happy Coding!
:)

这篇关于如何在sqlserver 2008中拆分多个字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！