[DATABASE/SQLSERVER] 구분자를 사용해 문자열 분리하기

icodebroker SQLSERVER 2024-06-18

■ 구분자를 사용해 문자열을 분리하는 방법을 보여준다.

▶ 예제 코드 (SQL)


CREATE FUNCTION [dbo].[GetTable]
(
    @String    NVARCHAR(MAX),
    @Separator NVARCHAR(10)
)
RETURNS @Result TABLE(Item NVARCHAR(100))
AS
BEGIN
    DECLARE @Temp   NVARCHAR(MAX)
    DECLARE @Start  INT
    DECLARE @End    INT
    DECLARE @Length INT

    SET @Temp  = ' ' + @String
    SET @Start = 1
    SET @End   = 0

    WHILE(@Start <> 0)
    BEGIN
        SET @End = CHARINDEX(@Separator, @Temp, @Start)

        IF @End <> 0
        BEGIN
            SET @Length = @End - @Start
        END
        ELSE
        BEGIN
            SET @Length = LEN(@Temp) - @Start + 1
        END

        INSERT INTO @Result(Item) VALUES (RTRIM(LTRIM(SUBSTRING(@Temp, @Start, @Length))))

        SET @Start = @End + 1

        IF @End = 0
        BEGIN
            RETURN
        END
    END

    RETURN
END

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

CREATE FUNCTION [dbo].[GetTable]

(

@String NVARCHAR(MAX),

@Separator NVARCHAR(10)

)

RETURNS @Result TABLE(Item NVARCHAR(100))

AS

BEGIN

DECLARE @Temp NVARCHAR(MAX)

DECLARE @Start INT

DECLARE @End INT

DECLARE @Length INT

SET @Temp = ' ' + @String

SET @Start = 1

SET @End = 0

WHILE(@Start <> 0)

BEGIN

SET @End = CHARINDEX(@Separator, @Temp, @Start)

IF @End <> 0

BEGIN

SET @Length = @End - @Start

END

ELSE

BEGIN

SET @Length = LEN(@Temp) - @Start + 1

END

INSERT INTO @Result(Item) VALUES (RTRIM(LTRIM(SUBSTRING(@Temp, @Start, @Length))))

SET @Start = @End + 1

IF @End = 0

BEGIN

RETURN

END

RETURN

END