Remove specific characters from a string in MySQL RRS feed

  • Question

  • User694411018 posted


    I will like to know how I can remove some specific characters is a string. 
    I have some strings of characters that contain this; [some_string this_id="1"]. I want to take out the number "1" name as "id_number" and remove the rest, [some_string this_id=""]. 

    Is this possible in MySQL? 

    Any help will be very appreciated. 


    Monday, July 27, 2015 8:41 AM

All replies

  • User269602965 posted

    MySQL supports REGEX SQL function for custom filtering/replacing characters within string



    Monday, July 27, 2015 2:10 PM
  • User694411018 posted

    Thank you for your reply.

    But I am not too sure how I would write the statement for getting just the number from [some_string this_id="1"]. And only when [some_string this_id="1"] exists in the string. 

    Not always the string will contain [some_string this_id="1"]. I guess I need to include a find and then extract the id from [some_string this_id="1"]. 

    Thank you

    Wednesday, July 29, 2015 9:49 AM
  • User694411018 posted

    This is what I have so far;

    SELECT post.post_date, post_content, LEFT(post.post_content, 
    LENGTH(post.post_content) - 36) as gallery_content_post, post.post_title, attachment_meta.meta_value AS upload_relative_path, substring(post.post_content,12, 1) as gallery_id
    FROM wp_posts AS post 
    LEFT OUTER JOIN wp_postmeta AS post_meta ON (post_meta.post_id = post.ID 
    AND post_meta.meta_key = '_thumbnail_id') 
    LEFT OUTER JOIN wp_postmeta AS attachment_meta ON 
    (attachment_meta.post_id = post_meta.meta_value 
    AND attachment_meta.meta_key = '_wp_attached_file') 
    WHERE post.post_status = 'publish'  
    AND post.post_type = 'post' and post.id=[some number id]

    This works fine only when the string contains [some_text id="2"]. When [some_text id="2"] don't exists in the string. It removes part of the content and for gallery_id returns an e.

    Wednesday, July 29, 2015 9:59 AM
  • User269602965 posted

    Showing few rows of sample data from the tables and cols in use would be helpful

    Wednesday, July 29, 2015 10:38 AM
  • User694411018 posted
    SELECT DISTINCT post_content, LEFT(post_content, LENGTH(post_content) - 36) as gallery_content_post, 
    post_title, substring(post_content,12, 1) as gallery_id
    FROM wp_posts
    WHERE id=[some id]

    post_content = string contains [some_text id="2"]
    LEFT(post_content, LENGTH(post_content) - 36) as gallery_content_post = here I am trying to remove [some_text id="2"]
    But if the string doesn't contains [some_text id="2"], it removes the last 36 characters. Returning an incomplete string.
    [some_text id="2"] = this a short code for a gallery in WordPress
    I am building a control outside of WordPress for an asp.net application.
    So far I have the content I need and the images for the galleries.
    Just need to find a way where I can remove the number id from [some_text id="2"] if it exists in content string. If it doesn't exists then return the content string complete. 

    Wednesday, July 29, 2015 1:29 PM
  • User269602965 posted

    Study function REPLACE to replace specific strings

    Wednesday, July 29, 2015 10:46 PM
  • User269602965 posted

    You can use INSTR to search for starting position of specific strings

    You can use CASE WHEN ELSE END to test for one or more conditions and transform data

    And as stated before, REGEX is a way to use complex rules to transform strings

    Wednesday, July 29, 2015 10:51 PM
  • User-124941 posted


    If you want to remove the specific characters then use this code, i think it will be helpful to you

    private static bool[] _lookup;
    static Program() {
       _lookup = new bool[65536];
       for (char c = '0'; c <= '9'; c++) _lookup[c] = true;
       for (char c = 'A'; c <= 'Z'; c++) _lookup[c] = true;
       for (char c = 'a'; c <= 'z'; c++) _lookup[c] = true;
       _lookup['.'] = true;
       _lookup['_'] = true;
    public static string RemoveSpecialCharacters(string str) {
       char[] buffer = new char[str.Length];
       int index = 0;
       foreach (char c in str) {
          if (_lookup[c]) {
             buffer[index] = c;
       return new string(buffer, 0, index);

    Saturday, December 12, 2015 9:14 AM
  • User-124941 posted

    thanks for saving it's a good move


    Monday, December 14, 2015 11:15 AM
  • User-146690165 posted

    Thanks a lot i really got some good information from here.....

    free online test 360

    Monday, October 3, 2016 11:33 AM