Alternative for parsename for more than 4 parts in sql server

Question

I am trying to split the , separated records in separated columns.

I am having 2 issues,

1st if I have more than 4 , separated values than parsename returns NULL

2nd If I have '.' in my rows than also it returns Null.

This is the query which I made and is working fine.

   declare @t table (items varchar(50))

   insert into @t values ('Apple,banana,mango,pineapple')

   select * from @t

   SELECT 
   PARSENAME(REPLACE(items,',','.'),4) Col1,
   PARSENAME(REPLACE(items,',','.'),3) Col2,
   PARSENAME(REPLACE(items,',','.'),2) Col3,
   PARSENAME(REPLACE(items,',','.'),1) Col4
   FROM @t

But this query returns null.

   declare @t table (items varchar(50))

   insert into @t values ('Apple.,banana,mango,pineapple')

   select * from @t

   SELECT 
   PARSENAME(REPLACE(items,',','.'),4) Col1,
   PARSENAME(REPLACE(items,',','.'),3) Col2,
   PARSENAME(REPLACE(items,',','.'),2) Col3,
   PARSENAME(REPLACE(items,',','.'),1) Col4
   FROM @t

Because of '.'

And also If I add another fruit in my item column it will return Null.

SO my question is how to split the comma separated items in different column if parsename is not working?

https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows should help you. — Jonathan Willcock, Jun 17 '19 at 06:43
PARSENAME is designed specifically to parse an sql object name. It can have four parts: the server name, the database name, the owner name, and the object name. If we give more than 4 parts, it will always return null. A custom function would help you. — MJoy, Jun 17 '19 at 06:43
*"I am trying to split the , separated records in separated columns."* Firstly, don't store comma-separated data in a table. Secondly, SQL is not a string processing language. Use a programming language better suited for this task. This is a one-time data scrubbing task, unless you are doing something wrong. It's not efficient to do this in SQL. — Tomalak, Jun 17 '19 at 07:19
Can't you just use `PARSENAME()` for the 3 names and use `REPLACE()` to get the 4th one? — Ilyes, Jun 17 '19 at 08:45
A good and long-term solution, would be to refactor your database design to not have multiple values in a single column value. Getting rid of constructs like what you have is a process called normalization. If you have no knowledge or experience with this concept or why it is a real good idea in relational databases, I suggest you research this. — TT., Jun 17 '19 at 10:28

Red Devil · Answer 1 · 2019-06-17T08:29:36.390

This is the function which works for me:

CREATE FUNCTION [dbo].[Split](
@str VARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @returnTable TABLE (idx INT PRIMARY KEY IDENTITY, item VARCHAR(8000))
AS
BEGIN
DECLARE @pos INT
SELECT @str = @str + @delimiter
WHILE LEN(@str) > 0 
    BEGIN
        SELECT @pos = CHARINDEX(@delimiter,@str)
        IF @pos = 1
            INSERT @returnTable (item)
                VALUES (NULL)
        ELSE
            INSERT @returnTable (item)
                VALUES (SUBSTRING(@str, 1, @pos-1))
        SELECT @str = SUBSTRING(@str, @pos+1, LEN(@str)-@pos)       
    END
RETURN
END

This is the final results:

declare @t table (items varchar(50))

INSERT INTO @t VALUES ('Apple.,banana,mango,pineapple,Cherry')

select 
(select item from fn_split(items,',') a where idx in (1)) as col1,
(select item from fn_split(items,',') a where idx in (2)) as col2,
(select item from fn_split(items,',') a where idx in (3)) as col3,
(select item from fn_split(items,',') a where idx in (4)) as col4,
(select item from fn_split(items,',') a where idx in (5)) as col5
 from @t

this worked for me although i call the function by Split as that is how it was named when i created it. Had to do a CTRL+Shift+R in order to get intellisense to recognize it and stop giving me the red squiggles — Robert Cadmire, Oct 07 '20 at 16:34
very good, fast and efficient function - way faster than my solution - thank you very much!!! — Josef B., Sep 22 '22 at 11:27

score 3 · Answer 2 · edited Dec 27 '20 at 23:45

Create the following function and it will work like ParseName but for any number of parts with any separator:

create FUNCTION GetStringParts(@SplittedString nvarchar(300), @Part int, @SplitChar nvarchar(1))
Returns nvarchar(300)

as
begin
if(@Part < 1)
        return '';
    declare @Index int = 1, @Result nvarchar(300) = @SplittedString, @PatIndexResult int
    while @Index < @Part
    begin
        set @PatIndexResult = PATINDEX('%' + @SplitChar + '%', @Result)
        if(@PatIndexResult = 0)
            return '';
        set @Result = SUBSTRING(@Result, @PatIndexResult + 1, len(@Result));
        set @Index = @Index + 1;
    end
    set @PatIndexResult = PATINDEX('%' + @SplitChar + '%', @Result);
    if(@PatIndexResult = 0)
        return @Result;
    return substring(@Result, 0, patindex('%' + @SplitChar + '%', @Result));
end

thats what exactly i want , thank you wael rbna ykramk – Ahmed Elsayed May 17 '23 at 03:28 — Ahmed Elsayed, May 17 '23 at 03:28

Alternative for parsename for more than 4 parts in sql server

2 Answers2