SELECT Replace(field_name, '[', '')

SELECT Replace(Replace(field_name, '[', ''), ']', '')

quote:

---

Originally posted by russell

Use the REPLACE() Function

Starting with this

SELECT Replace(field_name, '[', '')

you can extend it to

SELECT Replace(Replace(field_name, '[', ''), ']', '')

---

quote:

---

Originally posted by lieml

Hi guys,

How can I use replace statement in SQL query to replace/remove all the text inside the square bracket and the square bracket itself. I have many similar text like that with square bracket in my database, can I use regular expression or something similar?

E.g. Tom's doing math paper [10/08/2010 12:23 - admin]
---> Tom's doing math paper

Thanks

---

'Toms doing [blah] math paper  [10/08/2010 12:23 - admin]'

SELECT STUFF(@str, PATINDEX('%[[]%', @str), LEN(@str), '');

SELECT SUBSTRING(@str, 1, CHARINDEX('[', @str) - 1);

quote:

---

Originally posted by ms65g

>>
select stuff(@str,patindex('%(%',replace(@str,'[','(')),LEN(@str),'')
<<

It can be simpler like one of followings:

SELECT STUFF(@str, PATINDEX('%[[]%', @str), LEN(@str), '');

SELECT SUBSTRING(@str, 1, CHARINDEX('[', @str) - 1);

______________________

---

quote:

---

Originally posted by Idera

quote:

---

Originally posted by lieml

Hi guys,

How can I use replace statement in SQL query to replace/remove all the text inside the square bracket and the square bracket itself. I have many similar text like that with square bracket in my database, can I use regular expression or something similar?

E.g. Tom's doing math paper [10/08/2010 12:23 - admin]
---> Tom's doing math paper

Thanks

---

declare @str varchar(100)
select @str='Toms doing math paper [10/08/2010 12:23 - admin]'
select stuff(@str,patindex('%(%',replace(@str,'[','(')),LEN(@str),'')

I wonder what happens if there are multiple '['.Something like this

'Toms doing [blah] math paper  [10/08/2010 12:23 - admin]'

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

---

SELECT D.String
FROM table_name 
CROSS APPLY (SELECT substring + '' AS String
               FROM tvf_splitter(REPLACE(REPLACE(string, '[', ',['), ']', '],'), ',') D
              WHERE substring NOT LIKE '[[]%'
              ORDER BY D.n
               FOR XML PATH('')
             ) D

quote:

---

Originally posted by ms65g

>> I have the same question like Idera what happen if it has multiple square bracket?<<

An easy way is first split the string then filter item at last concatenate items.
You can use a inline TVF and CROSS APPLY for splitting.
For example:

SELECT D.String
FROM table_name 
CROSS APPLY (SELECT substring + '' AS String
               FROM tvf_splitter(REPLACE(REPLACE(string, '[', ',['), ']', '],'), ',') D
              WHERE substring NOT LIKE '[[]%'
              ORDER BY D.n
               FOR XML PATH('')
             ) D

______________________

---

--TVF for publishing and returning sequence numbers
 CREATE FUNCTION dbo.Numbers (@N INT) RETURNS TABLE AS
 RETURN (WITH RecCTE (nbr) AS 
         (SELECT 1 
          UNION ALL
          SELECT nbr + 1 FROM RecCTE WHERE nbr < 100),
    
          Nums (nbr) AS
         (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM RecCTE AS C1
                 CROSS APPLY
                 RecCTE AS C2
                 CROSS APPLY 
                 RecCTE AS C3)  
          SELECT nbr
          FROM Nums
          WHERE nbr <= @N);
 GO
 
 --Creating Number Table using TVF
 SELECT n.nbr
 INTO Nums
 FROM dbo.Numbers(10000) AS n;
 GO
 --TVF for Splitting the String 
 CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS 
 RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
                     ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
                 END AS Word, nbr
           FROM Nums
          WHERE nbr <= LEN(@S)
            AND SUBSTRING(@D + @S, nbr, 1) =@D);
 GO

SELECT word + ''
  FROM dbo.splitter (REPLACE(REPLACE('Toms doing [blah] math paper [10/08/2010 12:23 - admin]'
                       , '[', ',['), ']', '],'), ',') d
  WHERE word NOT LIKE '[[]%'
  ORDER BY nbr
  FOR XML PATH('')

quote:

---

Originally posted by ms65g

First execute these codes once:

--TVF for publishing and returning sequence numbers
 CREATE FUNCTION dbo.Numbers (@N INT) RETURNS TABLE AS
 RETURN (WITH RecCTE (nbr) AS 
         (SELECT 1 
          UNION ALL
          SELECT nbr + 1 FROM RecCTE WHERE nbr < 100),
    
          Nums (nbr) AS
         (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM RecCTE AS C1
                 CROSS APPLY
                 RecCTE AS C2
                 CROSS APPLY 
                 RecCTE AS C3)  
          SELECT nbr
          FROM Nums
          WHERE nbr <= @N);
 GO
 
 --Creating Number Table using TVF
 SELECT n.nbr
 INTO Nums
 FROM dbo.Numbers(10000) AS n;
 GO
 --TVF for Splitting the String 
 CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS 
 RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
                     ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
                 END AS Word, nbr
           FROM Nums
          WHERE nbr <= LEN(@S)
            AND SUBSTRING(@D + @S, nbr, 1) =@D);
 GO

Second try this:

SELECT word + ''
  FROM dbo.splitter (REPLACE(REPLACE('Toms doing [blah] math paper [10/08/2010 12:23 - admin]'
                       , '[', ',['), ']', '],'), ',') d
  WHERE word NOT LIKE '[[]%'
  ORDER BY nbr
  FOR XML PATH('')

______________________

---