如何在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

推荐答案

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中拆分多个字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！