0

After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is:

Table:

create table nulos ( nome varchar(max))
alter table nulos add cidade varchar(max)

values:

insert into nulos values ( 'rafael','são paulo'),('juliana',null)

I need to concatenate the values inside this table, to a insert, like this:

 select 'insert into nulos (nome,cidade) values ('+nome+','+cidade+')'
from nulos

And the result of the select:

insert into nulos (nome,cidade) values (rafael,são paulo)
NULL

how can I use the NULL value inside this concatenation? every answer says something to replace the null with '' or '_', But what I need it this:

insert into nulos (nome,cidade) values (rafael,são paulo)

insert into nulos (nome,cidade) values (Juliana,NULL)

SET CONCAT_NULL_YIELDS_NULL on is not an option because it just deletes the NULL value, and I need to insert it.

  • 5
    What is the context for this? you should be using parameterised queries if this is going anywhere near production. – Martin Smith Sep 25 '17 at 18:26
  • 2
    Do you need insert into nulos (nome,cidade) values (Juliana,NULL) in the result? Or insert into nulos (nome,cidade) values ('Juliana',NULL)? – ypercubeᵀᴹ Sep 25 '17 at 18:27
  • 1
    Why are you constructing dynamic queries here? Please tell me this isn't user input. – jpmc26 Sep 25 '17 at 22:15
6

Wrap your strings in single quotes so they will be input as strings. The NULL does not need quotes.

SELECT 'INSERT INTO nulos (nome,cidade) VALUES (' +
    CASE WHEN nome is null then 'NULL' ELSE ''''+
        REPLACE(nome,'''','''''')+'''' END + 
    ',' +
    CASE WHEN cidade is null then 'NULL' ELSE ''''+
        REPLACE(cidade,'''','''''')+'''' END +
    ')'
FROM nulos

This will give you:

INSERT INTO nulos (nome,cidade) VALUES ('rafael','são paulo')
INSERT INTO nulos (nome,cidade) VALUES ('Juliana',NULL)
  • 4
    @RacerSQL as long as none of your names are O'Brien Or Robert'); DROP TABLE Students;-- – Martin Smith Sep 25 '17 at 18:33
  • 1
    I added escaping for O'Brien. As far as the ; I didn't code for that because it wasn't part of the question but I agree that would be a whole other problem. I only do this (above) when scripting inserts from already cleaned data but yeah, it should not be used for unprotected inputs like front end apps. – indiri Sep 25 '17 at 18:43
  • 1
    As long as the quote is escaped correctly the second one (bobby tables) won't actually cause a problem either. The problem is the unescaped quote closes off the string literal there too. There are potential problems with homoglyph smuggling but not as written here. – Martin Smith Sep 25 '17 at 18:46
  • Oh yeah I've added the replace for it. Thanks both of you – Racer SQL Sep 25 '17 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.