SQL Server 2008 R2 - 如何拆分我的 varchar 列字符串并获取第三个索引字符串 [英] SQL Server 2008 R2 - How to split my varchar column string and get 3rd index string

问题描述

我在单列中有一条记录.如下所示

I have a record in a single column.Like below

Address
Arulraj   | Guindy   | Chennai | TamilNaadu | India | 600042 |  | 10000001 
Adaiakalm | Chenanai | Chennai | TamilNaadu | India | 600042 |  | 10000001 

如何分别获取拆分后的字符串TamilNaadu"、Chennai"、India".

How to get the splitted string "TamilNaadu", "Chennai ","India " separately.

推荐答案

这是一个将内联拆分字符串的函数....

Here's a function that will split a string inline....

CREATE FUNCTION [dbo].[FN_SPLIT] ( --SELECT DBO.FN_SPLIT('TEST1 , TEST2', 2, ',')
    @s varchar(512),
    @i int,
    @sep char(1) = ',')
RETURNS varchar(512)
AS
BEGIN
    DECLARE @Ret    VARCHAR(512);

    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT @Ret =
    RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))
    FROM Pieces
    WHERE pn = @i

    RETURN @Ret;
END

USAGE : SELECT DBO.FN_SPLIT('Address Arulraj | Guindy| Chennai | TamilNaadu | India | 600042 | | 10000001 ', 3, '|')

返回:钦奈

这是一个表值函数，它将返回一个值表

Here's a table valued function that will return a table of values

CREATE FUNCTION [dbo].[FN_SPLIT_TBL](@InExp varchar(8000), @Sep varchar(10)) --SELECT * FROM DBO.[FN_SPLIT_TBL]('TEST1,TEST2', ',')
RETURNS @Res    TABLE(
    Pos         int,
    Value       varchar(max))
AS
BEGIN
    WITH Pieces(pn, start, stop) AS (
        SELECT 1, 1, CHARINDEX(@Sep, @InExp)
        UNION ALL
        SELECT pn + 1, stop + 1, CHARINDEX(@sep, @InExp, stop + 1)
        FROM Pieces
        WHERE stop > 0
    )

    INSERT INTO @Res
    SELECT pn, SUBSTRING(@InExp, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces OPTION (MAXRECURSION 0);

    RETURN;
END

USAGE : SELECT *
FROM DBO.FN_SPLIT_TBL('Address Arulraj | Guindy| Chennai | TamilNaadu | India | 600042 | | 10000001 ', '|')
--WHERE Pos = 3 -- Uncomment this to only return index 3

返回:

Pos Value
1   Address Arulraj 
2    Guindy
3    Chennai 
4    TamilNaadu 
5    India 
6    600042 
7    
8    10000001 

这篇关于SQL Server 2008 R2 - 如何拆分我的 varchar 列字符串并获取第三个索引字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！