question

jehenjoshi-1189 avatar image
0 Votes"
jehenjoshi-1189 asked jehenjoshi-1189 commented

manupulate strings in sql server

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.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jehenjoshi-1189,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered jehenjoshi-1189 commented

Hi @jehenjoshi-1189,

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much. The output is as expected.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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);


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.