Phil Phil - 5 months ago 10
SQL Question

Replace pattern in string PHP REGEX

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?

Answer

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".

Comments