Can't convert a simple string manipulation operation to a dynamic SQL version in a T-SQL stored procedure

Bernard Thouin
Bernard Thouin used Ask the Experts™
I have to convert a select statement in a T-SQL stored procedure to dynamic SQL to be able to run that procedure in different DBs, and one line in it fails constantly, it's the following (before conversion):

Left(SecuritiesAccNo, charindex('-', SecuritiesAccNo) -1)

It's a simple statement, taking the content of the SecuritiesAccNo field (defined as nvarchar(18)) from a table, and isolating the characters left of a hyphen found in the field normally at the 7th or 8th character.

I just can't seem to find the version that, as a string, will be executed correctly as part of an exec (@sql) statement. I always get an error with all the various versions I tried.

Can somebody help me there, I'm despairing.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

It may be NULL's related and I suggest add a ISNULL or COLEASCE function on the column. Or simply try use PATINDEX instead of CHARINDEX as both CHARINDEX and PATINDEX are used to get starting position of a pattern. The functional difference is that the PATINDEX can use wild characters in the pattern being searched whereas CHARINDEX can't.
Bernard ThouinIT Analyst and developer



Thanks for your answer, but in the meantime, I found the right syntax, a bit by chance :) Sorry for the bother.
IT Analyst and developer
The dynamic SQL version was actually very simple, I had convinced myself of the contrary :(

It was just:

Left(SecuritiesAccNo,  charindex(''-'', SecuritiesAccNo) - 1)

Instead of:

Left(SecuritiesAccNo,  charindex('-', SecuritiesAccNo) - 1)

So I solved my problem !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial