manupulate strings in sql server

Question

I have a string like "WITH
MEMBER [Service Rate].[Service Rate].[#~~fdd-7796-4295-a59d-affdfd~~#] AS AGGREGATE([#~~fdfdf-4b4e-4e3a-b0f1-fdfd~~#])
, Caption='Current Period' , SOLVE_ORDER=0 , Scope_Isolation=CUBE
SET [#~~8195f161-4b4e-4e3a-b0f1-b2cda0773122~~#] AS {[Service Rate].[Service Rate].[Service Months].&[2000 - MAR]&[2000]&[2000 - Q1]:
[Service Rate].[Service Rate].[Service Months].&[2015 - FEB]&[2015]&[2015 - Q1]}
SELECT { DISTINCT ( { [Rate Brand Indicator].[Rate Brand Indicator].&[0] ,
[Rate Brand Indicator].[Rate Brand Indicator].&[Y] , [Rate Brand Indicator].[Rate Brand Indicator].&[N] } ) }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON COLUMNS,
{ DISTINCT ( { [Service Rate].[Service Rate].[#~~asfd-fd-dfd-a59d-b820f67a591f~~#] } ) }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON ROWS FROM [Cost]
WHERE ( [Measures].[Paid per day] , [Rate Type].[Rate Type].&[2] ,
[Group].[First Group].&[CHILD''S Dept] ) CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME "

Is there any function or sql script that gives me output in different rows like
[Service Rate].[Service Rate]
[Rate Brand Indicator].[Rate Brand Indicator]
[Measures].[Paid per day]
[Rate Type].[Rate Type]
[Group].[First Group]

Basically I want to get text within large brackets.

Answer 1

Hi @jehen joshi ,

Welcome to Microsoft Q&A!

Please refer below function and check whether it is helpful:

CREATE FUNCTION dbo.Manupulatestrings(@s varchar(max))  
RETURNS @output_table TABLE(Value VARCHAR(max))  
AS  
BEGIN  
while (1=1)  
begin  
	if (CHARINDEX('[',@s) <> 0) AND charindex(']', @s) > charindex('[', @s) AND CHARINDEX('.[',@s) <>0  
	begin       
		INSERT INTO @output_table (Value)  
		select SUBSTRING(@s,CHARINDEX('].[',@s)-CHARINDEX('[',REVERSE(SUBSTRING(@s,1,CHARINDEX('].[',@s))))+1,  
		(CHARINDEX('].[',@s)+CHARINDEX(']',SUBSTRING(@s,CHARINDEX('].[',@s),LEN(@S)-CHARINDEX('].[',@s)+1),CHARINDEX(']',SUBSTRING(@s,CHARINDEX('].[',@s),LEN(@S)-CHARINDEX('].[',@s)+1))+1))-(CHARINDEX('].[',@s)-CHARINDEX('[',REVERSE(SUBSTRING(@s,1,CHARINDEX('].[',@s))))+1))  
		set @s = SUBSTRING(@S,CHARINDEX('].[',@s)+CHARINDEX(']',SUBSTRING(@s,CHARINDEX('].[',@s),LEN(@S)-CHARINDEX('].[',@s)+1),CHARINDEX(']',SUBSTRING(@s,CHARINDEX('].[',@s),LEN(@S)-CHARINDEX('].[',@s)+1))+1),1000)  
	end  
else  
break  
end  
  
RETURN;  
END  
GO  

Call this function as below:

declare @s varchar(max)  
 set @s = N'WITH  
  MEMBER [Service Rate].[Service Rate].[#~~fdd-7796-4295-a59d-affdfd~~#] AS AGGREGATE([#~~fdfdf-4b4e-4e3a-b0f1-fdfd~~#])  
  , Caption=''Current Period'' , SOLVE_ORDER=0 , Scope_Isolation=CUBE  
  SET [#~~8195f161-4b4e-4e3a-b0f1-b2cda0773122~~#] AS {[Service Rate].[Service Rate].[Service Months].&[2000 - MAR]&[2000]&[2000 - Q1]:  
  [Service Rate].[Service Rate].[Service Months].&[2015 - FEB]&[2015]&[2015 - Q1]}  
  SELECT { DISTINCT ( { [Rate Brand Indicator].[Rate Brand Indicator].&[0] ,  
  [Rate Brand Indicator].[Rate Brand Indicator].&[Y] , [Rate Brand Indicator].[Rate Brand Indicator].&[N] } ) }  
  PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON COLUMNS,  
  { DISTINCT ( { [Service Rate].[Service Rate].[#~~asfd-fd-dfd-a59d-b820f67a591f~~#] } ) }  
  PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON ROWS FROM [Cost]  
  WHERE ( [Measures].[Paid per day] , [Rate Type].[Rate Type].&[2] ,  
  [Group].[First Group].&[CHILD''S Dept] ) CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME';  
  
select distinct * from dbo.Manupulatestrings(@s)  
 ORDER BY VALUE DESC  

Output:

VALUE  
[Service Rate].[Service Rate]  
[Rate Type].[Rate Type]  
[Rate Brand Indicator].[Rate Brand Indicator]  
[Measures].[Paid per day]  
[Group].[First Group]  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Create a function like this:

CREATE FUNCTION [dbo].[udf_RetrieveItemsFromString] (
    @inputString nvarchar(max)
)
RETURNS 
    @TBL TABLE (Item varchar(100) NOT NULL) 
AS
BEGIN
    DECLARE @SearchItems TABLE (
        RowId int IDENTITY(1, 1) NOT NULL,
        Item varchar(100) NOT NULL
    );

    INSERT INTO @SearchItems VALUES
    ('[Service Rate].[Service Rate]'),
    ('[Rate Brand Indicator].[Rate Brand Indicator]'),
    ('[Measures].[Paid per day]'),
    ('[Rate Type].[Rate Type]'),
    ('[Group].[First Group]');

    DECLARE @rowId int = 1;
    DECLARE @maxRowId int;
    DECLARE @item varchar(100);

    SELECT @maxRowId = MAX(RowId) FROM @SearchItems;

    WHILE @rowId <= @maxRowId
    BEGIN
        SELECT @item = Item FROM @SearchItems WHERE RowId = @rowId;

        IF CHARINDEX(@item, @inputString) > 0
        BEGIN
            INSERT INTO @TBL VALUES (@item);
        END

        SET @rowId = @rowId + 1;
    END

    RETURN;
END
GO

And then you can get the output:

DECLARE @s nvarchar(max);
SET @s = N'WITH
MEMBER [Service Rate].[Service Rate].[#~~fdd-7796-4295-a59d-affdfd~~#] AS AGGREGATE([#~~fdfdf-4b4e-4e3a-b0f1-fdfd~~#])
, Caption=''Current Period'' , SOLVE_ORDER=0 , Scope_Isolation=CUBE
SET [#~~8195f161-4b4e-4e3a-b0f1-b2cda0773122~~#] AS {[Service Rate].[Service Rate].[Service Months].&[2000 - MAR]&[2000]&[2000 - Q1]:
[Service Rate].[Service Rate].[Service Months].&[2015 - FEB]&[2015]&[2015 - Q1]}
SELECT { DISTINCT ( { [Rate Brand Indicator].[Rate Brand Indicator].&[0] ,
[Rate Brand Indicator].[Rate Brand Indicator].&[Y] , [Rate Brand Indicator].[Rate Brand Indicator].&[N] } ) }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON COLUMNS,
{ DISTINCT ( { [Service Rate].[Service Rate].[#~~asfd-fd-dfd-a59d-b820f67a591f~~#] } ) }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_KEY, MEMBER_TYPE ON ROWS FROM [Cost]
WHERE ( [Measures].[Paid per day] , [Rate Type].[Rate Type].&[2] ,
[Group].[First Group].&[CHILD''S Dept] ) CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME';

SELECT * FROM [dbo].[udf_RetrieveItemsFromString](@s);