MSQL SELECT with REPLACE function

Applications Web Development
1

Hello,

Im trying to select values after comma seperated values.
Im trying to do this with the SUBSTRING_INDEX function, however the substring index checks after the comma but there is also a whitespace included after the comma, Is there a way to easily replace the whitespaces with nothing? So SUBSTRING_INDEX can read this out?

because of the whitespaces after the comma, Substring_index doenst read it correctly.
Hopefully you guys can help me out!

This is the querry code :

SELECT
	sku,
	`name`,
	barcode,
	idproduct,
	ean,
	bsku
	#REPLACE(picqer_products.barcode," ", "")
FROM
	picqer_products
	INNER JOIN bol_lvb_inventory ON bol_lvb_inventory.ean = REPLACE(SUBSTRING_INDEX((SUBSTRING_INDEX(picqer_products.barcode , ' ,', 1 )), ',',- 1 ), ' ', '' )
WHERE
	barcode LIKE '%753759173692%'
2

@Remo , welcome to the forum , we hope you find it friendly and useful.

can you confirm the actual db product and version being used.

please provide

  • a small sample of data being retrieved
  • the results from the query as posted
  • the desired/expected results of that data post processing.

NB: please provide text output NOT screenshots

thks

3

I will look into it and give the desired info tomorrow as soon im on my work again.

4

After some research i managed to fix it with 2 seperated querries,

First i went trough the first database to get the desired information with this querry >
$sql2 = $conSticker->prepare("SELECT sku, `name`, barcode, idproduct FROM picqer_products WHERE barcode LIKE '%$str%' ");

After this querry, i used php explode to put these values of barcode into an array.

$new = explode(",", $row2->barcode);

After this i used the array on the next MYSQL querry to get the values i need with implode.

$sql = $conSticker->prepare("SELECT ean, bsku FROM bol_lvb_inventory WHERE ean IN (". implode(",", $new).")");

I managed to get it working, So this question is solved!
Thanks for the reply anyways.

1 Like
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.