Escaping a close bracket ')' in an EXEC string.

Question

Escaping a close bracket ')' in an EXEC string.

Don.

Ten Centuries

Points: 1293
More actions
March 31, 2014 at 7:51 am

#287171

Hi guys,
Im having a problem with the EXEC function in an update program.
When running a script I get the message:
"The charindex function requires 2 to 3 arguments"
In one of the scripts I have a CASE statement that needs to find the index of a ')'
I'm querying a field (Ref) to get the ')'
Select CHARINDEX(')', Ref)
This is something like the EXEC script
EXEC ( ' Select CHARINDEX('')'', @Ref) ')
I'm thinking that the compiler sees the CHARINDEX('') and thinks it needs to execute that.
What can I do about this?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The arguments to CharIndex need to be in '', so there's no need to escape them. Anything inside '' is treated as a string literal. Can you post the code? If it's dynamic SQL, it's possible the quotes haven't been escaped properly

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

One way of simplifying this is to use the NCHAR() function;

EXEC ( ' Select CHARINDEX(NCHAR(41), @Ref) ')

Don. Ten Centuries Points: 1293 More actions · Answer 3

Hi GilaMonster,

Thanks for the quick reply.

The reason the quotes are escaped is because they're in an EXEC string.

The script works fine when run on its own.

The problem is it needs to be enclosed in EXEC ( ' xxxxxxxxx ')

I've enclosed the full script on the chance it might be of use.

EXEC ( '

INSERT INTO RSM_FULLTEXT ( ACCOUNTNO, CONTACT, RECID, ORIGIN, NOTES, FLD_LENGTH)

SELECT ACCOUNTNO,

CASE WHEN (CHARINDEX(''(oc:'', Ref) > 0) AND (CHARINDEX('')'', REVERSE(Ref)) = 1)

THEN SUBSTRING(LEFT(SUBSTRING(REF, CHARINDEX(''(oc:'', Ref) + 4, LEN(REF)), LEN(SUBSTRING(REF, CHARINDEX(''(oc:'', Ref) + 4, LEN(REF)))-1), 0, 40)

ELSE

CASE WHEN (CHARINDEX(''(oc:'', Ref) > 0)

THEN SUBSTRING(RIGHT(REF, LEN(REF) - (CHARINDEX(''(oc:'', Ref)) -

CASE WHEN LEN(REF) = 80 THEN 3 ELSE 2 END), 0, 40)

ELSE ''''

END

END AS ''CONTACT'',

RECID, ''HISTORY'', CAST(CAST(CH.NOTES as VARBINARY(MAX)) as VARCHAR(MAX)) AS NOTES, DATALENGTH(CH.NOTES) AS FLD_LENGTH

FROM CONTHIST CH WHERE CH.NOTES IS NOT NULL

AND NOT EXISTS (SELECT * FROM RSM_FULLTEXT RF WHERE RF.ACCOUNTNO = CH.ACCOUNTNO

AND RF.RECID = CH.RECID AND RF.ORIGIN = ''HISTORY'')

')

Don. Ten Centuries Points: 1293 More actions · Answer 4

Eirikur Eiriksson (3/31/2014)
One way of simplifying this is to use the NCHAR() function;
EXEC ( ' Select CHARINDEX(NCHAR(41), @Ref) ')

Hi Eirikur,

That looks really promising. 🙂

Let me test.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That script runs without error for me. Are you sure that's the exact statement which is throwing the error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Don. Ten Centuries Points: 1293 More actions · Answer 6

Hi Guys,

I've been testing and there must be a problem with the update application.

As mentioned earlier the scripts run fine when run through SQL.

Thanks again for your help.