Remove specific characters from a string in MySQL

Question

User694411018 posted

Hi, 

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. 

Thanks!

Answer 1

User269602965 posted

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

https://dev.mysql.com/doc/refman/5.1/en/regexp.html

 

Answer 2

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

Answer 3

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.

Answer 4

User269602965 posted

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

Answer 5

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. 

Answer 6

User269602965 posted

Study function REPLACE to replace specific strings

Answer 7

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

Answer 8

User-124941 posted

Hi,

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;
         index++;
      }
   }
   return new string(buffer, 0, index);
}

http://www.gurujobalert.com/

Answer 9

User-124941 posted

thanks for saving it's a good move

http://www.gurujobalert.com/

Answer 10

User-146690165 posted

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

free online test 360