What mistake I am making in this sql query
Declare @str Varchar(100) = 'asasa,bsasas,csasa,dsczxvvc'
declare @d varchar(2)=','
SELECT
RIGHT(LEFT(@str,Number-1),
CHARINDEX(@d,REVERSE(LEFT(@d+@str,Number-1))))
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@str)
AND SUBSTRING(@str,Number,1) = @d
Expected Result
(No column name)
asasa
bsasas
csasa
dsczxvvc
Actual Result
(No column name)
asasa
bsasas
csasa
Your AND SUBSTRING(@str, Number, 1) = @d is forcing a comma to be at the end of dsczxvvc...there isn't one. asasa,bsasas,csasa,dsczxvvc, works.
add one more comma at the end of string
you can directly add comma in the string like = "asasa,bsasas,csasa,dsczxvvc,"
or
handle this thing in sql side.
Declare @str Varchar(100) set @str = 'asasa,bsasas,csasa,dsczxvvc' declare @d varchar(2) set @d =','
set @str = @str + ','
SELECT RIGHT(LEFT(@str,Number-1), CHARINDEX(@d,REVERSE(LEFT(@d+@str,Number-1)))) FROM master..spt_values WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(@str) AND SUBSTRING(@str,Number,1) = @d
This is because since the last string portion does not have the seperator at the end of the string If you add the following code just before the SELECT statement, it will work
set @str = @str + @d
There are many split functions on the web, you can use one of them actually.
