-2

Maybe simple, but in the Moment a Problem for me?!

How to insert a string like 'D'Artagnan' via SQL INSERT to a Database Table?

I've tryied:

DECLARE @spcChar nvarchar(1);
DECLARE @2spcChar nvarchar(2);
DECLARE @newString nvarchar(50);
DECLARE @tmpChar nvarchar(50);

SET @spcChar = CHAR(39);

SET @tmpStr = 'D'Artagnan'; -> FAIL
SET @tmpStr = 'D'+ @spcChar + 'Artagnan';

SET @2spcChar = @spcChar + @spcChar;

SET @newString = REPLACE( @tmpStr, @spcChar, @2spcChar );

-- Return the result of the function
RETURN @newString;    -> == 'D'!!!

Not very pretty, but just for testing

JustMe
  • 1
  • 1
  • 1
    Please the database you are using. – Allan Wind Mar 26 '21 at 07:54
  • Your last comment to my answer suggest you are asking about how to deal with that in a programming language, so update question and tag it with the programming language too. – Allan Wind Mar 26 '21 at 08:24
  • @JustMe have you considered quoting the value - values like `"` and `'` and '\` are all involved with identifiers and need to be quoted - https://stackoverflow.com/questions/11321491 – Mr R Mar 26 '21 at 21:41

2 Answers2

1

You encode a quote in SQL by double up:

select 'D''Artagnan';

which returns:

D'Artagnan

If you are executing queries via a programming language, read the value into a variable, say, name, then bind that variable to query. You haven't told me what language you used but it could look like this:

sql = 'insert into t values (?)'
db.execute(sql, name)
Allan Wind
  • 23,068
  • 5
  • 28
  • 38
  • double up the quote is all i want to do, but how to do in SQL? Programming in C++, C#, ... is no Problen, but i want to recognize single quotes and change it to double qoutes after the user typed the text?! so my ScalarFunctio automatically doubles them if nessecary. Like: INSERT INTO table( ... ) VALUES( dbo.scfFunc( text ), ... ); If text has quote, they will be doubled, if not nothing should happen, scfFunc is where the Magic happens – JustMe Mar 26 '21 at 08:12
  • 1
    You asked how to insert a literal string, and you encode the ' by doubling up. If you are using a programming language, then you read the value into a variable, then bind that variable to a position/name (often ?) and don't worry about it. – Allan Wind Mar 26 '21 at 08:22
  • By adding Rows to a table, the User usually types 'D'Artagnan'. But this gives an error. What don't gives an error ist something like: 'D''Artagnan'. What i want to do is change (1)D'Artagnan to (2)'D'+CHAR(39)+'Artagnan' And my idea now was to use a Function that 'catches' the Userinput, checks Input (1) and returns (2) if needed. But is INSERT the right place to check anyway? It's 'Microsoft SQL Server Management Studio 17', User edits table by using 'Edit top nnn lines' – JustMe Mar 26 '21 at 08:57
  • Please update your question to show a simplified version of your code. This sounds like a classic XY problem. Allan has already told you what you need to do. – Stu Mar 26 '21 at 09:04
  • @JustMe . . . To repeat what Allan has already said: Use parameters to pass values. Don't munge query strings with literal values. Quotes are only one example of problems that you can encounter. – Gordon Linoff Mar 26 '21 at 11:43
0

What i want to do is change D'Artagnan( User Input ) to 'D'+CHAR(39)+'Artagnan'

And my idea now was to use a Function that 'catches' the Userinput, checks Input (1) and returns (2) if needed.

But is INSERT the right place to check anyway?

It's 'Microsoft SQL Server Management Studio 17', User edits table by using 'Edit top nnn lines'

JustMe
  • 1
  • 1