CODEWITHSUNDEEP
mysqlsql
ab21
ab21

Reputation: 43

remove text between square brackets

I am trying to remove the text between the square brackets, however it only seems to remove the brackets.

SELECT Replace(Replace(aud_desc, '[', ''), ']', '') from _audit

aud_desc is This is [a] test however with the above I have got it to show This is a test, I am not sure why its not removing the text in the brackets as well.

Have I missed something?

Upvotes: 4

Views: 7431

Answers (5)

Paulo Cuellas
Paulo Cuellas

Reputation: 21

Following https://stackoverflow.com/users/2641576/matt idea, this is the solution for SQL Server:

SELECT REPLACE(aud_desc, SUBSTRING(aud_desc, CHARINDEX('[', aud_desc), LEN(aud_desc) - CHARINDEX(']', REVERSE(aud_desc)) - CHARINDEX('[', aud_desc) + 2), '')   AS aud_desc
FROM _audit

Upvotes: 0

jceddy
jceddy

Reputation: 1947

Here's a user-defined function that does it and works with multiple bracket pairs in the string:

DELIMITER //
CREATE FUNCTION REMOVE_BRACKETS(input TEXT CHARACTER SET utf8 COLLATE utf8_bin)
  RETURNS TEXT CHARACTER SET utf8 COLLATE utf8_bin
  BEGIN
    DECLARE output TEXT CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '';
    DECLARE in_brackets BOOL DEFAULT FALSE;
    DECLARE length INT;

    IF(input IS NULL)
    THEN
      RETURN NULL;
    END IF;

    WHILE(TRUE) DO
      SET length = LOCATE(CASE WHEN in_parens THEN ']' ELSE '[' END, input);
      IF(length = 0)
      THEN
        RETURN CONCAT(output, input);
      END IF;

      IF(in_brackets)
      THEN
        SET in_brackets = FALSE;
      ELSE
        SET output = CONCAT(output, SUBSTRING(input, 1, length - 1));
        SET in_brackets = TRUE;
      END IF;

      SET input = SUBSTRING(input, length + 1);
    END WHILE;
  END //
DELIMITER ;

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6854

try this

SELECT CONCAT(SUBSTRING_INDEX(aud_desc,'[',1),SUBSTRING_INDEX(aud_desc,']',-1)) 
FROM _audit;

Upvotes: 0

Matt
Matt

Reputation: 15061

Use the REPLACE, LENGTH, LOCATE, REVERSE & SUBSTRINGfunctions.

SELECT REPLACE(aud_desc, SUBSTRING(aud_desc, LOCATE('[', aud_desc), LENGTH(aud_desc) - LOCATE(']', REVERSE(aud_desc)) - LOCATE('[', aud_desc) + 2), '') AS aud_desc
FROM _audit

Input:

aud_desc

word [brakcet] word
[brakcet] word
word [brakcet]

Output:

aud_desc

word word
word
word

SQL Fiddle: http://sqlfiddle.com/#!9/178bb/1/0

Upvotes: 7

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

This query should not remove text in the brackets at all. All it does - is replaces [ and ] characters with empty character, thus removing them.

To remove text in the brackets you can use locate function to get position of [, then one more time locate to get position of ] and then use substr function to get appropriate parts of string according to [] symbols positions.

Upvotes: 0

Related Questions