asked on

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

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

lcohan

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 Thouin

ASKER

HI

Thanks for your answer, but in the meantime, I found the right syntax, a bit by chance :) Sorry for the bother.

ASKER CERTIFIED SOLUTION

Bernard Thouin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial