SQL question

[Paul Kemp]

I’ve got a list or array of ‘1’,’b’,’c’,’69’ and I want to turn it into ‘’1’’,’’b’’,’’c’’,’’69’’ to use in an openquery.
Without having actually tried anything yet, is using replace chr(39) with chr(34) an option?
How would you go about doing this?
@TribbleZA since you don’t charge an arm and a leg, feel free to send invoice

 

[TribbleZA]

I presume your list is in a table? And this is MS SQL? Or is this a query? Why do you need to store the " as part of the data?

This might help a bit https://groups.google.com/g/comp.databases.ms-access/c/z1k1xKsrHNw?pli=1

So, my findings were, if the character is ' then do:

temp = temp & char(34) 'result is two single quotes

if the character is " then do:

temp = temp & char(39) 'result is two double quotes

So a replace should work - though I have never done this personally.

Here is another option:

I use a function I call SQLQuoteFix myself that fixes both
cases for me, and call it whenever I'm working with a field that might
contain either a single or double quote:
>
> Public Function SQLQuoteFix(TextIn As String) As String
> SQLQuoteFix = Replace(TextIn,
> Chr$(39), _
> Chr$(39) & Chr$(39))
> SQLQuoteFix = Replace(SQLQuoteFix, _
> Chr$(34), _
> Chr$(34) & Chr$(34))
> End Function

 

[Paul Kemp]

I presume your list is in a table? And this is MS SQL? Or is this a query? Why do you need to store the " as part of the data?

Yes MS. Stored proc. List is a parameter which I want to pass to openquery. If it is single inverted it will escape the query, you have to use doubles.

Will give it a bash, haven’t actually tried anything yet. Just thought I’d ask as I haven’t had to do this sort of thing before. Typically just use the linked server or a package to get data but I’m dealing with some performance issues with things we’ve just migrated from an old server.

Writing a function is going to be my first choice here.

Thank you.

 

[Baxteen]

dont over think this there is an easier way

declare @yourString varchar(100),
@varToFind varchar(10),
@varToReplaceWith varchar(10)

set @yourString = '''1'',''b'',''c'',''69'''

set @varToFind = ''''
set @varToReplaceWith = ''''''



SELECT REPLACE(@yourString, @varToFind, @varToReplaceWith);

 

[Paul Kemp]

dont over think this there is an easier way

Seems simple but what you have in the @yourString is what I want in the end.

Let me rather get my hands on the keyboard a little later. Posting from my phone, I may even have the initial string value I receive wrong.

 

[Baxteen]

I thought you had the apostrophe wrapped strong already and you want to double it for an open query.
in that case:


just replace the comma with an apostrophe comma and concat an apostrophe at the start and end

declare @yourString varchar(100),
@varToFind varchar(10),
@varToReplaceWith varchar(10)

set @yourString = '1,b,c,69'

set @varToFind = ','
set @varToReplaceWith = ''','''



SELECT concat('''',REPLACE(@yourString, @varToFind, @varToReplaceWith),'''');

 

[Paul Kemp]

I thought you had the apostrophe wrapped strong already and you want to double it for an open query.
in that case:


just replace the comma with an apostrophe comma and concat an apostrophe at the start and end

My explanation sucked. Also typing on the phone doesn’t make my quotes show correctly but your solution does work. I’m not replacing single quotes with doubles but rather replacing a single quote with two single quotes.
In openquery you need to double your escapes to pass a list across.
In short, to get it working I’m replacing char(39) with char(39)+char(39). Haven’t tested it out on the stored proc but it will work.
Muchos gracias.

 

[Kosmik]

not sure what code you using to generate the string but a simple replace string/character type function would do it for you.

Dynamic sql is a lovely minefield.

 

[W@P]

I’ve got a list or array of ‘1’,’b’,’c’,’69’ and I want to turn it into ‘’1’’,’’b’’,’’c’’,’’69’’ to use in an openquery.
Without having actually tried anything yet, is using replace chr(39) with chr(34) an option?
How would you go about doing this?
@TribbleZA since you don’t charge an arm and a leg, feel free to send invoice

 

[Paul Kemp]

not sure what code you using to generate the string but a simple replace string/character type function would do it for you.

Dynamic sql is a lovely minefield.

Forgot to reply, it’s a 10 year old report used by our production team. The correct thing to do would be to fix the report but that has some human complications I don’t feel like getting into.

The replace worked like a charm and took a linked server query that took over an hour to run down to 3 minutes using openquery. Again, the linked server issue is fixable but in typical Microsoft fashion fixing one thing breaks another.

 

[Kosmik]

Forgot to reply, it’s a 10 year old report used by our production team. The correct thing to do would be to fix the report but that has some human complications I don’t feel like getting into.

The replace worked like a charm and took a linked server query that took over an hour to run down to 3 minutes using openquery. Again, the linked server issue is fixable but in typical Microsoft fashion fixing one thing breaks another.

ok I'm having deja vu of scripts I wrote back in the 2000's here, hope you not at that company. They left my code running for years.... simple maxim: if it aint broke , leave it alone.

Thing is those were written VERY long ago, sql 2003 days etc.