How to remove inbetween empty comma in mysql query

Question

3.67/5 (2 votes)

See more:

actual data (,,,demo, ,xxxx,,,yyy,,,) to be (demo,xxxx,yyy)

What I have tried:

i had tried

SQL

SELECT TRIM(BOTH ',' FROM ',,,demo, ,xxxx,,,yyy,,,');
SELECT REPLACE(TRIM(TRIM(',' FROM ',,,demo, ,xxxx,,,yyy,,,')), ',,', ',');

its not removing the empty comma in between any one plz help??

Posted 3-Apr-16 7:59am

Arun-23

Updated 5-Apr-16 12:18pm

Patrice T

v6

Add a Solution

Comments

RickZeeland 3-Apr-16 14:53pm

You seem to expect that the TRIM() function will replace all occurrences of the comma, but in fact it stops at "demo" (leading) and "yyy" (trailing).
I'm not an MySQL specialist, but I found this at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php

Mohibur Rashid 3-Apr-16 19:20pm

Try splitting your string by comma and then drop all empties

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pandu web dev · Answer 1 · 2016-04-05T06:08:00

Solution 1

Clarification required: what is this one in the query

',,,demo, ,xxxx,,,yyy,,,'

Mysql is not really meant for text manipulation

If you are using a server side language like php, you can preg_replace to remove commas in the string and send that variable to select query.

Posted 5-Apr-16 6:08am

pandu web dev

Comments

Arun-23 6-Apr-16 7:54am

',,,demo, ,xxxx,,,yyy,,,' this is in the field of a table
Table -> 'test' has field name called 'A' which has ',,,demo, ,xxxx,,,yyy,,,'

A
--
,,,demo, ,xxxx,,,yyy,,,

W∴ Balboos, *GHB* · Answer 2 · 2016-04-05T06:53:00

By implication (that you're using .php in your subject list) I'll presume you're generating the query.

In this case, as you add each value, if it's empty(), then insert NULL (no single quotes!) after the comma.

Alternatively, you're not generating the query directly with php, but it's being passed in to your php. In this case, you can fix all but the last comma with str_replace(',,', ',NULL,', yourString); and, since NULL's are presumbly allowed, you can the use Trim() to get rid of the last comma - or, if the last character in your string is a comma, add NULL to the end of your string.

The above is heavily dependent upon how you handle missing data: is this going to a stored procedure with default values? A table with default values? And also, how did the commas get there in the first place?