Click here to Skip to main content
14,937,212 members
Please Sign up or sign in to vote.
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
Updated 5-Apr-16 12:18pm
v6
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

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.
   
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,,,
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?



   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900