Sql While Loop Exists

I'm trying to get a list of dates from one cell in SQL. These dates are all in one cell in SQL '....d|43460)())(0||1(d|42534)())(0||2(d|40910)())(0||3(d|41855)...' I've managed to

Solution 1:

DECLARE @clndr_data varchar(MAX)    =   'd|43460)())(0||1(d|42534)())(0||2(d|40910)())(0||3(d|41855)'
DECLARE @walker     int             =   0
DECLARE @holder     varchar(MAX)    =   @clndr_data
DECLARE @date       varchar(5)
DECLARE @tempTb     table([value] varchar(50))

WHILE CHARINDEX('d|', @holder) > 0
BEGIN
    SET @walker = CHARINDEX('d|', @holder) + 2
    SET @date = (SELECT SUBSTRING(@holder, @walker, 5))
    INSERT INTO @tempTb VALUES (@date)
    SET @holder = SUBSTRING(@holder, @walker + 5, LEN(@holder))
END

SELECT * FROM @tempTb

Just replace @clndr_data with your actual clndr_data column. Don't forget to set @holder variable to the value of this column.

SET @holder = (SELECT clndr_data FROM [x].[dbo].[CALENDAR] WHERE clndr_id = '954')

DECLARE @walker     int             =   0
DECLARE @holder     varchar(MAX)
DECLARE @date       varchar(5)
DECLARE @tempTb     table([value] varchar(50))

SET @holder = (SELECT clndr_data FROM [x].[dbo].[CALENDAR] WHERE clndr_id = '954')

WHILE CHARINDEX('d|', @holder) > 0
BEGIN
    SET @walker = CHARINDEX('d|', @holder) + 2
    SET @date = (SELECT SUBSTRING(@holder, @walker, 5))
    INSERT INTO @tempTb VALUES (@date)
    SET @holder = SUBSTRING(@holder, @walker + 5, LEN(@holder))
END

SELECT * FROM @tempTb

Solution 2:

you can treat clndr_data as a delimited string and make use of delimited string splitter to split it into multiple item in a list. Here i am using ")(" as delimiter

Here i am using DelimitedSplit8K() by Jeff Moden from http://www.sqlservercentral.com/articles/Tally+Table/72993/

Note : you will need to create the function first

SELECT  clndr_data, right(Item, 5) as Number
FROM    [x].[dbo].[CALENDAR]
        CROSS APPLY dbo.DelimitedSplit8K(clndr_data, ')(')
where   clndr_id ='954'and     Item    like'%d|%'