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™
on
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.

Thanks
Bernard
Comment
Watch Question

Do more with

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

Commented:
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

Author

Commented:
HI

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
Commented:
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