Replace pattern in string PHP REGEX

Question

I have a string that is used for an INSERT command. When values are empty, the corresponding spot in the INSERT string is being generated as:

VALUES ('', 'VALUE', '', '', '', 0, 1, '')

I want to replace the '' with NULL so the database no longer throws an error about casting varchar to type numeric:

VALUES (null, 'VALUE', null, null, null, 0, 1, null)

I tried using:

$build_values_m = str_replace($build_values_m, ", '', ", ", null, ");

However, that takes away most of my INSERT string and does not JUST replace the empty values with NULL.

How can I do this?

Are you saying that you want to replace "''" with "NULL"? That is not what your str_replace is attempting to do (not even close). — kainaw
– kainaw, Commented Jun 16, 2016 at 12:25
@kainaw, yes if my string is VALUES('','VALUE','','','',0,1,'') I want it to say VAULES(null,'VALUE',null,null,null,0,1,null) — Phil
– Phil, Commented Jun 16, 2016 at 12:26

kainaw · Accepted Answer · 2016-06-16 12:29:10Z

1

It looks like you want to replace "''" with "NULL" and that you are attempting to do so without glancing at the manual: http://php.net/manual/en/function.str-replace.php

If you looked at the manual, you would see that the order of the parameters is Search, Replace, OriginalString. You are trying to do OriginalString, Search, Replace - which won't work - obviously. So, try to do it in the proper order:

$build_values_m = str_replace("''","NULL",$build_values_m);

Then, all instances of "''" will be replaced with the string "NULL".

answered Jun 16, 2016 at 12:29

kainaw

4,3621 gold badge23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Over a year ago

@kainawa - so very sorry, it's too early here I wasn't thinking properly. You are correct I had the arguments of str_replace mixed up.

Anwar · Accepted Answer · 2016-06-16 12:35:47Z

1

You can perform replacement using regex replacement function preg_replace() like following :

$string = "VALUES ('','','VALUE HERE','',)";
$pattern = array();
$pattern[0] = '/\'\'/g'; // Match : ''
$replacement = array();
$replacement[0] = 'NULL';

preg_replace($pattern, $replacement, $string);

answered Jun 16, 2016 at 12:35

Anwar

4,3065 gold badges46 silver badges67 bronze badges

1 Comment

Phil Over a year ago

this does work however kainaw pointed out first that I was calling str_replace incorrectly. Thank you for your answer though!

Collectives™ on Stack Overflow

Replace pattern in string PHP REGEX

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related