none
sql Query to replace the square bracket and the whole text inside with a single charecter

    Question

  • Hi All,

    I  have a question.

    I am using Sql Server 2012.

    I have a  situation where i need to replace the entire text and the square bracket with a single charecter.

    Eg:-> 

    I have atext    san[123456dd]text

    i just wanted to replace all teh text inside the sqaure bracket to another charecter say 'X' 

    My end result should be sanXtext.

    Could anyone help me regarding this?


    Samproo

    Thursday, February 20, 2014 7:26 PM

Answers

  • In this case you may try recursive cte solution, e.g.

    DECLARE @t VARCHAR(100) = 'san[123456dd]text[898989dd]note[oo9999]';
    
    WITH cte
    AS (
    	SELECT @t AS [Original String]
    		,CASE 
    			WHEN charindex('[', @t) > 0
    				AND charindex(']', @t) > charindex('[', @t)
    				THEN stuff(@t, charindex('[', @t), charindex(']', @t) - charindex('[', @t) + 1, 'X')
    			ELSE @t
    			END AS [New String]
    		,0 AS [Level]
    	
    	UNION ALL
    	
    	SELECT [Original String]
    		,stuff([New String], charindex('[', [New String]), charindex(']', [New String]) - charindex('[', [New String]) + 1, 'X') AS [New String]
    		,[Level] + 1 AS [Level]
    	FROM cte
    	WHERE charindex('[',[New String]) > 0
    	)
    	,cte1
    AS (
    	SELECT *
    		,row_number() OVER (
    			PARTITION BY [Original String] ORDER BY [Level] DESC
    			) AS Rn
    	FROM cte
    	)
    SELECT *
    FROM cte1
    WHERE Rn = 1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Kalman TothModerator Friday, February 21, 2014 12:30 AM
    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 9:23 PM
  • Try:

    DECLARE @t VARCHAR(100) = ' san[123456dd]text'
    
    SELECT @t AS [Original String]
    	,CASE 
    		WHEN charindex('[', @t) > 0
    			AND charindex(']', @t) > charindex('[', @t)
    			THEN stuff(@t, charindex('[', @t), charindex(']', @t) - charindex('[', @t) + 1, 'X')
    		ELSE @t
    		END AS [New String]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Venkat786 Thursday, February 20, 2014 8:01 PM
    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 7:33 PM
  • This will work to replace the first set of square brackets in your text

    DECLARE @youText VARCHAR(50) = 'san[123456dd]text'
    DECLARE @replacementChar VARCHAR(1) = 'X'
    
    SELECT LEFT(@youText, CHARINDEX('[', @youText) - 1) + @replacementChar + RIGHT(@youText, LEN(@youText) - CHARINDEX(']', @youText))
    

    If you have multiple sets of brackets then you'll need to do the replace multiple times

    • Marked as answer by Samproe Thursday, February 20, 2014 8:30 PM
    Thursday, February 20, 2014 7:44 PM
  • Hey,

    Check if this helps you with multiple instances of Bracketed text . .Sorry couldnt test ..

    DECLARE @Text VARCHAR(50) = 'san[123456dd]text'
    DECLARE @replacementChar VARCHAR(1) = 'X'
    
    ;with cte
    as
    (
    SELECT 
    STUFF(@Text,charindex('[',@Text),charindex(']',@Text)-charindex('[',@Text),@replacementChar) as text,1 as level
    UNION ALL
    SELECT STUFF(Text,charindex('[',Text),charindex(']',Text)-charindex('[',Text),@replacementChar) as text,level+1 as level
    FROM cte
    WHERE CHARINDEX('[',text)>0 
    )
    SELECT TOP 1 text
    FROM cte
    ORDER BY level desc


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 8:24 PM

All replies

  • Try:

    DECLARE @t VARCHAR(100) = ' san[123456dd]text'
    
    SELECT @t AS [Original String]
    	,CASE 
    		WHEN charindex('[', @t) > 0
    			AND charindex(']', @t) > charindex('[', @t)
    			THEN stuff(@t, charindex('[', @t), charindex(']', @t) - charindex('[', @t) + 1, 'X')
    		ELSE @t
    		END AS [New String]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Venkat786 Thursday, February 20, 2014 8:01 PM
    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 7:33 PM
  • declare @a varchar(30)
    set @a='san[123456dd]text'
    
    
    select replace(@a,substring(@a,charindex('[',@a),charindex(']',@a)-charindex('[',@a)+1),'X')
    --Prashanth
    Thursday, February 20, 2014 7:35 PM
  • This will work to replace the first set of square brackets in your text

    DECLARE @youText VARCHAR(50) = 'san[123456dd]text'
    DECLARE @replacementChar VARCHAR(1) = 'X'
    
    SELECT LEFT(@youText, CHARINDEX('[', @youText) - 1) + @replacementChar + RIGHT(@youText, LEN(@youText) - CHARINDEX(']', @youText))
    

    If you have multiple sets of brackets then you'll need to do the replace multiple times

    • Marked as answer by Samproe Thursday, February 20, 2014 8:30 PM
    Thursday, February 20, 2014 7:44 PM
  • Hey Samproo,

    check if this works for you..

    DECLARE @Text VARCHAR(50) = 'san[123456dd]text'
    DECLARE @replacementChar VARCHAR(1) = 'X'
    
    SELECT 
    STUFF(@Text,charindex('[',@Text),charindex(']',@Text)-charindex('[',@Text),@replacementChar)



    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Thursday, February 20, 2014 8:11 PM
  • Prashanth ,

    Thanks 

    It works if i have a single square bracket like 

    'san[123456dd]text'

    but presently i do have multiple square brackets

    like

    'san[123456dd]text[898989dd]note'

    I wanted to have teh result as

    'sanXtextXnote'



    Samproo

    Thursday, February 20, 2014 8:18 PM
  • Hi Naomi ,

    It works ... But I do have cases where we have multiple square brackets like 

    'san[123456dd]text[898989dd]note[oo9999]'

    i wanted the result to be 

    sanXtextXnoteX


    Samproo

    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    • Unmarked as answer by Naomi NModerator Friday, February 21, 2014 5:15 PM
    Thursday, February 20, 2014 8:20 PM
  • Hey,

    Check if this helps you with multiple instances of Bracketed text . .Sorry couldnt test ..

    DECLARE @Text VARCHAR(50) = 'san[123456dd]text'
    DECLARE @replacementChar VARCHAR(1) = 'X'
    
    ;with cte
    as
    (
    SELECT 
    STUFF(@Text,charindex('[',@Text),charindex(']',@Text)-charindex('[',@Text),@replacementChar) as text,1 as level
    UNION ALL
    SELECT STUFF(Text,charindex('[',Text),charindex(']',Text)-charindex('[',Text),@replacementChar) as text,level+1 as level
    FROM cte
    WHERE CHARINDEX('[',text)>0 
    )
    SELECT TOP 1 text
    FROM cte
    ORDER BY level desc


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 8:24 PM
  • Thanks Jay,

    I just tried , But it didnot work. 

    when i searched for 2 square brackets , i was getting Null  result.



    Samproo

    Thursday, February 20, 2014 8:37 PM
  • thank you ..

    It works 


    Samproo

    Thursday, February 20, 2014 8:49 PM
  • In this case you may try recursive cte solution, e.g.

    DECLARE @t VARCHAR(100) = 'san[123456dd]text[898989dd]note[oo9999]';
    
    WITH cte
    AS (
    	SELECT @t AS [Original String]
    		,CASE 
    			WHEN charindex('[', @t) > 0
    				AND charindex(']', @t) > charindex('[', @t)
    				THEN stuff(@t, charindex('[', @t), charindex(']', @t) - charindex('[', @t) + 1, 'X')
    			ELSE @t
    			END AS [New String]
    		,0 AS [Level]
    	
    	UNION ALL
    	
    	SELECT [Original String]
    		,stuff([New String], charindex('[', [New String]), charindex(']', [New String]) - charindex('[', [New String]) + 1, 'X') AS [New String]
    		,[Level] + 1 AS [Level]
    	FROM cte
    	WHERE charindex('[',[New String]) > 0
    	)
    	,cte1
    AS (
    	SELECT *
    		,row_number() OVER (
    			PARTITION BY [Original String] ORDER BY [Level] DESC
    			) AS Rn
    	FROM cte
    	)
    SELECT *
    FROM cte1
    WHERE Rn = 1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Kalman TothModerator Friday, February 21, 2014 12:30 AM
    • Marked as answer by Samproe Friday, February 21, 2014 3:02 PM
    Thursday, February 20, 2014 9:23 PM
  • Samproo,

    There was a missing '+1' for the length field of stuff which has been corrected. Hope this does the job for you ..

    DECLARE @Text VARCHAR(50) 
    SET @text= '[123]san[12]text[fd123][123453]'
    DECLARE @replacementChar VARCHAR(1) 
    SET @replacementchar= 'X'
    
    ;with cte
    as
    (
    SELECT 
    STUFF(@Text,charindex('[',@Text),charindex(']',@Text)-charindex('[',@Text)+1,@replacementChar) as text,1 as level
    UNION ALL
    SELECT STUFF(Text,charindex('[',Text),charindex(']',Text)-charindex('[',Text)+1,@replacementChar) as text,level+1 as level
    FROM cte
    WHERE CHARINDEX('[',text)>0 
    )
    SELECT TOP 1 text
    FROM cte
    ORDER BY level desc


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Friday, February 21, 2014 7:12 AM